cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Total weekday payment for last 3 months

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 

 Capture.PNG

 

 

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

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

 

1 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




Lima - Peru

View solution in original post

7 REPLIES 7
Helper IV
Helper IV

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

@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




Lima - Peru

View solution in original post

@Vvelarde, Thanks. It works.

 

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

@JulietZhu

 

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

 

Regards

 

Victor




Lima - Peru

Hi @JulietZhu,

 

@Vvelarde soluition should work as intended.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helper IV
Helper IV

@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

Super User III
Super User III

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors