Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
I also put dummy data in original post. Can someboday help? Thanks
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
Hi, I Just Added the ALL Function to evaluate all the dates in your calendar table according to your filter.
Regards
Victor
Hi @JulietZhu,
@Vvelarde soluition should work as intended.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |