UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Power Bi And Custom Date Diff    
 
   
mac
post Jan 26 2020, 01:53 PM
Post#1



Posts: 172
Joined: 16-July 03
From: Northern MN


Hello all,

I have a client that is requesting that I calculate a date difference between two dates in Power BI that excludes Friday, Saturday and Sunday. I know how to exclude weekends, but am struggling to find a way to exclude Friday's in addition using DAX (either column or measure).

Can anyone point me in the right direction? Is this possible?


Thanks,

Mac
Go to the top of the page
 
MadPiet
post Jan 26 2020, 03:02 PM
Post#2



Posts: 3,486
Joined: 27-February 09



Sure it is. You have to use CALCULATE() and then use FILTER() inside...

Something like

CODE
CALCULATE(
        COUNTROWS ( DimDate ),
        FILTER( 'DimDate',
            OR( 'DimDate'[DayNumberOfWeek] > 1, 'DimDate'[DayNumberOfWeek] < 6 )
             )
    )


Then you'd have to filter for the date range you want... If you're looking at the current evaluation context, it's something like
CODE
FILTER ( 'DimDate', [TheDate] >= [StartDateMeasure] AND [TheDate] <= [EndDateMeasure] )


So something like...

CODE
CALCULATE(
        COUNTROWS ( DimDate ),
        FILTER( 'DimDate',
                       'DimDate'[FullDateAlternatekey] >= [StartDateMeasure],
                       'DimDate'[FullDateAlternatekey] <= [EndDateMeasure],
            OR( 'DimDate'[DayNumberOfWeek] > 1, 'DimDate'[DayNumberOfWeek] < 6 )
             )
    )


One really good place to read some helpful date-related DAX is on Ferrari & Russo's website www.daxpatterns.com
This post has been edited by MadPiet: Jan 26 2020, 03:03 PM
Go to the top of the page
 
mac
post Jan 26 2020, 03:09 PM
Post#3



Posts: 172
Joined: 16-July 03
From: Northern MN


Thanks for the reply MadPiet, I will give it a try. Really appreciate it, will let you know how it goes. Does this formula need to be entered as a column or measure?

Mac
Go to the top of the page
 
MadPiet
post Jan 26 2020, 03:47 PM
Post#4



Posts: 3,486
Joined: 27-February 09



It's a measure.

It's much easier to do complex DAX measures if you build them out of simple ones. A really good place to start is Rob Collie's book... check out his website: powerpivotpro.com
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th February 2020 - 10:52 PM