Need help on writing DAX queries based date and time

[Anonymous]

On the 8th business day of a month onwards I would like to show the data till last month (Dec)and before the 8th business day I would like to show the data for the month before the last month(Nov). 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could add another measure as rank Business day measure. then modify your meaure.

1. rank measure.

rank = RANKX(FILTER(ALL(Table1),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0)&&WEEKDAY([Date],2)<6),CALCULATE(MIN([Date])),,ASC)

2.modify your measure.

YTD = 
VAR EightBusinessday =
     MINX(FILTER(ALL(Table1),EOMONTH([Date],0)=EOMONTH(MAX('Table1'[Date]),0)&&[rank]=8),[Date])
VAR EndDate =
    IF (
        TODAY () < EightBusinessday,
        EOMONTH ( TODAY (), -2 ),
        EOMONTH ( TODAY (), -1 )
    )
RETURN
    CALCULATE (
        FactLaborBudgetHourBV[M_BudgetLaborHour],
        KEEPFILTERS ( DimDate[Date] <= EndDate )
    )

 The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you

PREVIEW

[ValtteriN]

Hi,

You can create a measure with this kind of logic:

var _date = calculate(max(calendar[business day]),all(calendar),calendar[date]=today()) return
IF(_date<8,[measure with data until Novermeber],[measure with data until december])

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[Anonymous]

The below was the query written, Kindly let me know ho we can change as per the logic

YTD =

VAR EightBusinessday =

CALCULATE (

MIN ( DimDate[Date] ),

DimDate[CurrentMonthBusinessDay] = 15

)

VAR EndDate =

IF (

TODAY () < EightBusinessday,

EOMONTH( TODAY (), -2 ),

EOMONTH ( TODAY (), -1 )

)

RETURN

CALCULATE (

FactLaborBudgetHourBV[M_BudgetLaborHour],

KEEPFILTERS( DimDate[Date] <= EndDate )

)

[amitchandak]

@Anonymous , Assume you have two measure like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Then you can have a measure 

if(day(Today()) < 8 , [MTD Sales],[last MTD Sales])