Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 wher...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak,

     

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

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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?