How to calculate current month and previous month values group by date?

[PowerBITesting]

How to calculate current month and previous month values?

[amitchandak]

@PowerBITesting , Can you time intelligence with date table

example measures

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]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('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])

or without TI

//Column in date table
Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)	

measures
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

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


Appreciate your Kudos.

[PowerBITesting]

@amitchandak  this value is empty in Matrix , seems like previous month value is not calculated . In matrix I dint added any date field , only I need contact and total $ and % of change between current and last month .Do I need to add anything else?

last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

[v-zhenbw-msft · Accepted solution]

Hi @PowerBITesting ,

We have a little confused, what is the calculation logic of difference?

Do you want to display the sum of the Spent of the max date and the Spent of the previous month?

If yes, we can create a measure to meet your requirement.

Measure = 
VAR _max_date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[Contact name] = MAX ( 'Table'[Contact name] ) )
    )
VAR _last_month =
    DATE ( YEAR ( _max_date ), MONTH ( _max_date ) - 1, DAY ( _max_date ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Spent] ),
        FILTER ( 'Table', 'Table'[Date] >= _last_month && 'Table'[Date] <= _max_date )
    )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.