Dynamic Measure based on Dates

[Anonymous]

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!

[amitchandak · Accepted solution]

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-bi

Appreciate 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/

[amitchandak · Accepted solution]

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-bi

Appreciate 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/

[Anonymous]

Hi @amitchandak,

Thank you for the response, but this did not work.  Here is the error I've received.

[amitchandak · Accepted solution]

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))

[Anonymous]

Did not mean to mark this as a solution but I can't figure out how to unmark it.

This unfortunately did not work either.  Let me explain this in a little bit more detail.

I am pulling in a table called 'pbi TableInventTransStagingV1' - let's just call in the Inventory Transactions table.  This table contains all inventory transactions and has a field called Quantity and a Date Field called DATEFINANCIAL.  What I'm trying to do is recreate a report in Dynamics 365 that displays a Beginning Balance, Ending balance and transactions in between.  The Beginning Balance referes to the balance available at the beginning of that day.  

Since our organization went live with Dynamics 365 on 11/12/2017, that Beginning Balance field is made up of all transactions (Quantity) after 11/11/2017 and before whatever date (DATEFINANCIAL) is selected.  So what I'm trying to do is have a slicer at the top for DATEFINANCIAL, and create a measure that displays the total Quantity where the date is greater than 11/11/2017 and less than the minimum of the dates selected in the slicer.  

Any idea on how to get to this solution?

[Anonymous]

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