Create Measure that calculates sum for current month and previous month

[Anonymous]

Hi, 

I have a table with the column Date that goes on for 12 months, let's say and Total Amount. I would like to create two different measures: one that will always show me the sum of the Current Month, and another measure that gives me the sum of the Previous Month.

I create a Measure using calculate and sum, but it was empty.

Thanks for your help.

[amitchandak · Accepted solution]

@Anonymous , We can use time intelligence with date table.

example :

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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[danextian · Accepted solution]

Hi @Anonymous,

The answer will depend on how you define current and previous month - are they based on today's date or on the slicer selection? Here's my take on this  assuming that current month is based on the slicer selection.
Create these measures:

Previous Month Start - Current Filter Context = 
EOMONTH ( MAX ( 'Table'[Date] ), -2 ) + 1

Previous Month End - Current Filter Context = 
EOMONTH ( MAX ( 'Table'[Date] ), -1 )

Current Month Start - Current Filter Context = 
EOMONTH ( MAX ( 'Table'[Date] ), -1 ) + 1

Current Month End - Current Filter Context = 
EOMONTH ( MAX ( 'Table'[Date] ), 0 )

Previous Month's Value = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    DATESBETWEEN (
        'Table'[Date],
        [Previous Month Start - Current Filter Context],
        [Previous Month End - Current Filter Context]
    )
)

Current Month's Value = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    DATESBETWEEN (
        'Table'[Date],
        [Current Month Start - Current Filter Context],
        [Current Month End - Current Filter Context]
    )
)

Change MAX ( 'Table'[Date] ) to TODAY() if current month is based on the current date.

[amitchandak · Accepted solution]

@Anonymous , We can use time intelligence with date table.

example :

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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.