Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Month over Month difference in Running Hours

Calculate the month over month change in running hours for a Counter key   I found a way to calculate difference over a period of time, but now need to now how much increase in running hours (readi...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

In case you have a date, create date calendar and use datesmtd or totalmtd

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

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

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Yes this helped

    I figured it out.

    with an if statement if there are missing month to return 0

    with a max statement to take highest recorded value when multiple values are teaken a month

    • sturlaws's avatar
      sturlaws
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

      Hi Anonymous 

      For your measure [Last MTD Reading], I would rename it and write it like this:

       

       

      Reading Previous MTD =
CALCULATE (
    SUM ( 'Hour Reading'[Reading] );
    FILTER (
        ALL ( dates );
        dates[YearMonthNumber]
            = MAX ( dates[YearMonthNumber] ) - 1
            && dates[Date] <= MAX ( dates[previous month date] )
    )
)

       

       

      To get this measure to work you will have to add some columns to your calendar table.


      I created a sample report based on the data you provided, so you can see some of the other measures and the various columns needed in the calendar-table

      sample report 


      Cheers,
      Sturla