Total weekday payment for last 3 months

[JulietZhu]

Need calcute the total weekday payment for past 3 months. Use the following DAX, but return blank. IsWorkingDay column in calendar table shows 1 for weekday, 0 for weekend. The logic is correct, but there is something wrong with DAX. Can someboday piont it out? Thanks 

 

 Here is dummy data after using today() as suggested. But still not working.

https://1drv.ms/u/s!AlYpYKwSuOKxhD7iWANsYZzbEVii

[JulietZhu]

I also put dummy data in original post. Can someboday help? Thanks

[Vvelarde · Accepted solution]

@JulietZhu

Try with this:

_SumPaymentWeekdayLast3M2 =
CALCULATE (
    SUM ( DataFromDB[Payment_Amt] );
    FILTER (
        ALL ( CalendarTable );
        CalendarTable[Date]
            <= EOMONTH ( TODAY (); -4 ) + 1
            && CalendarTable[Date] <= EOMONTH ( TODAY (); -1 )
            && CalendarTable[IsWorkingDay] = 1
    )
)

Regards

Victor

Lima - Peru

[JulietZhu]

@Vvelarde, Thanks. It works.

Would you please explain more about your DAX? Why my DAX doesn't work?

[Vvelarde]

@JulietZhu

Hi, I Just Added the ALL Function to evaluate all the dates in your calendar table according to your filter.

Regards

Victor

[MFelix]

Hi @JulietZhu,

@Vvelarde soluition should work as intended.

Regards,

MFelix

[JulietZhu]

@MFelix,  I changed to your formua, but still not working. Please see my dummy data.

I have caculated total payment for last 3 monthes inculding weekend.  I also need total payment for last 3 monthes exculding weekend. You can see my DAX for exclude weekend and it shows blank. Not sure where is the problem. Thanks

https://1drv.ms/u/s!AlYpYKwSuOKxhD7iWANsYZzbEVii

[MFelix]

Hi @JulietZhu,

Believe that your issue is with the MAX(CalendarTable[Date]), I'm assuming that you have a calendar table that goes from until the end of the year at least but your data only as data until current date right?

When you make a MAX on calendar table it will get the last date of the table if you have no other filter/slicer on your table this will return 31/12/2018 (for example) so your measure will give out blank() you need to add a sliceror change your measure to 

EMONTH(Today (); -1)

instead of

EMONTH(MAX (CalendarTable[Date]); -1)

Regards,

MFelix