Calculate Month over Month difference in Running Hours

[Anonymous]

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 (readings) there are per counter key each month.

[amitchandak · Accepted solution]

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]

Hi @Anonymous ,

If i understand you correctly, you will need a CALENDAR table and use the month as time period. Please show more details if you need further help with DAX.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

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 · Accepted solution]

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]

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

[sturlaws]

Hi @Anonymous ,

I think you need to rephrase you question. The link is to an internal sharepoint site, so it is not accessible to anyone outside your organisation.

Cheers,
Sturla

[Anonymous]

how can i attach a file?

[sturlaws]

You have to upload it to dropbox/onedrive/google drive/other and share the link

Cheers,
Sturla
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490