Forum Discussion
Dynamic Measure based on Dates
Hi all,
I'm trying to build a measure for a Beginning Balance. The 'Beginning Balance' measure should be the sum of the Quantity field shown below, but should only sum up the total quantity where the Date Phyiscal field is > 11/11/2017 and less than whatever date is selected in the slicer. For instance, if I wanted to see today's beginning balance, I'd want to see the total quantity where the Date Phyiscal is >11/11/2017 and <2/5/2020. How do I do this?
Thanks in advance!
Hope you had a calendar date table joined with the physical date. Try
total qty = var _max = maxx(date,date[date]) return calculate(sum(table[qty]),filter(all(date),table[Date Phyiscal]>=date(year(2017),month(11),day(11)) &&table[Date Phyiscal]<=_max))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-biAppreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/My Mistake
Move all date means calendar out. Or use calendar[date]
total qty = var _max = maxx(date,date[date]) return calculate(sum(table[qty]),all(date),filter(table,table[Date Phyiscal]>=date(year(2017),month(11),day(11)) &&table[Date Phyiscal]<=_max)) total qty = var _max = maxx(date,date[date]) return calculate(sum(table[qty]),all(date),filter(table,date[Date]>=date(year(2017),month(11),day(11)) &&date[Date]<=_max))
5 Replies
Hope you had a calendar date table joined with the physical date. Try
total qty = var _max = maxx(date,date[date]) return calculate(sum(table[qty]),filter(all(date),table[Date Phyiscal]>=date(year(2017),month(11),day(11)) &&table[Date Phyiscal]<=_max))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-biAppreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/My Mistake
Move all date means calendar out. Or use calendar[date]
total qty = var _max = maxx(date,date[date]) return calculate(sum(table[qty]),all(date),filter(table,table[Date Phyiscal]>=date(year(2017),month(11),day(11)) &&table[Date Phyiscal]<=_max)) total qty = var _max = maxx(date,date[date]) return calculate(sum(table[qty]),all(date),filter(table,date[Date]>=date(year(2017),month(11),day(11)) &&date[Date]<=_max))
Hey Anonymous
You can use SUMX to do this: https://docs.microsoft.com/en-us/dax/sumx-function-dax
Or CALCULATE(SUM(SILTER())) like in this thread: https://community.powerbi.com/t5/Desktop/Sumx-with-date-filter/td-p/726040
Anotehr example: https://community.powerbi.com/t5/Desktop/How-to-sum-values-by-period-based-on-the-start-and-end-may-cross/td-p/22367
If you need a dynamic date in the calculation you can use TODAY() and DATEADD if necessary: https://docs.microsoft.com/en-us/dax/dateadd-function-dax