MTD for workingday

[pvtrinh89]

Dear Experts,
I have facing with a issue in generating a report that showing up the Amount MTD .

Assuming i have 2 tables including:

Fact( Amount,date...),

TimeDim( Date, workingday,month,Quater,year, ...) 

The workingday field in TimeDim is only Y or N, then now i want to calculate the MTD for date that has Workingday=Y.

For example, In June 2016, there are 5,12,19,26 have workingday =N, so now i want TotalMTD of 6thJune = TotalMTD of 4thJune (because day 5 is not a workingday) and TotalMTD of 7thJune = TotalMTD of 4thJune + Amount of 6thJune!!!..

I am so sorry for bad explaination, i hope you can understand i mean totally!

Thank in advanced.

Best regards,
Michael J.

[ankitpatira · Accepted solution]

@pvtrinh89 if i understood you correctly you want sum of amount of all dates except where working day is N. First make sure both tables are related ie active relationship exists between date column of both the tables. then create measure using below DAX, replacing 'sam' and 'sam2' with your table names.

CALCULATE(SUM(sam[amount]), sam2[workingday] = "Y")

[pvtrinh89]

@ankitpatira Thank for your help!
Its is not correct totally, but depend on your advice, i have resolved my issue with the filter at my DAX.!

Have a nce weekend.

Thank yoou so much.

Best regards.