Get value of maximum date of month selected in the slicer and subtract maximum value of next month

[bml123]

Hi,

I have this data in the table as below

Id	Date	Amount
1	01/04/2021	100
1	10/04/2021	50
1	20/05/2021	75
1	25/05/2021	120

In the slicer, I have calendar date in month and year format, for eg if I choose April 2021

I need to get the maxumum value of the month selected in the slicer which is April and the maximum date of April in the table is 10/04/2021 and the value of that is 50.

I also need to get the maximum value of the next month selected in the slicer which is May and the maximum date of May in the table is 25/05/2021 and value of that is 120

So, the final value should be 120-50=70 for id=1. How do I achieve that?

[v-shex-msft · Accepted solution]

HI @bml123,

Please break the relationship from the calendar table to the fact table to let the slicer works as a selector instead of a filter, then you can try to use the following measure formula to achieve your requirement:

measure =
VAR selected =
    MAX ( 'Calendar'[Date] )
VAR cMonth =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( selected )
                && MONTH ( [Date] ) = MONTH ( selected )
        ),
        VALUES ( Table[ID] )
    )
VAR nMonth =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( selected )
                && MONTH ( [Date] )
                    = MONTH ( selected ) + 1
        ),
        VALUES ( Table[ID] )
    )
RETURN
    CALCULATE (
        MAX ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = nMonth ),
        VALUES ( Table[ID] )
    )
        - CALCULATE (
            MAX ( Table[Amount] ),
            FILTER ( ALLSELECTED ( Table ), [Date] = cMonth ),
            VALUES ( Table[ID] )
        )

Regards,

Xiaoxin Sheng

[bml123]

Hi @amitchandak,

I don't want the sum, I just want the value of the maximum date of the month selected for previous and next month. Also I need the value for each id.  How do I achieve that?

[amitchandak]

@bml123 , I have used lastnonblankvalue for that purpose. Idea is to make it generic. Also lastnonblankvalue need measure so I used sum. 

Second, if you use a date table it will handle taking max for each ID

Another option is , check the measure below

Measure =
var _min = maxx(allselected(Table), Table[Date])
var _max = maxx(filter(all(Table) , eomonth(table[Date],0) = eomonth(_min,1)), Table[Date])
return
calculate(sum(Table[Amount]), filter(all(table), Table[Date] = _max)) - calculate(sum(Table[Amount]), filter(all(table), Table[Date] = _min))

[amitchandak]

@bml123 , Use a date table joined with your date of the table, then try measures

This month =

calculate(lastnonblankvalue('Date'[Date], sum(Table[Amount])), datesmtd('Date'[Date]))

next month =

calculate(lastnonblankvalue('Date'[Date], sum(Table[Amount])), datesmtd(dateadd('Date'[Date],1,month)))

or

next month =

calculate(lastnonblankvalue('Date'[Date], sum(Table[Amount])), nextmonth('Date'[Date]))

diff = [Next month] -[This 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 :radacad sqlbi My Video Series Appreciate your Kudos.

[bml123]

Hi @amitchandak 

I tried both your suggestions and i got very big numbers and are not right.

[amitchandak]

@bml123 , please find the file

Please check diff total, how to correct grand total, you might need that for current and next too

[amitchandak]

@bml123 , Updated file

Current and Next use time intelligence and total are wrong.

Current 1 and Next 1 do not use time intelligence and total are wrong.

The current total and Next total are built on current 1 and the next 1 and the totals are correct. they can be built on current and next

Diff total and diff 1 have correct totals.

Code for correct total is also important