Show last month of data on total

[thod]

Hi,
I want my measure to show the latest month of data for current selection on the totals. If an attribute is blank in latest month for the current selection, it should not show anything in the total for the sepecific attribute. However, my current measure does not do what I want it to as it show data for nov in total for attribute A (see example).
So if the user selects months Oct, Nov, Dec, it should show data for each month however the totals should represent what is shown in Dec.
If the user selects months Oct, Nov, it should show data for each month however the totals should represent what is shown in Nov.
My measure looks like this:

VAR MaxDate =
MAX( 'FactTable'[DW_SK_Date] )
VAR Result =
CALCULATE(
SUM( 'FactTable'[Column1] ),
'DateTable'[DW_SK_Date] = MaxDate
)
RETURN
Result

Year	2022	Total
Attribute	Oct	Nov	Dec	Total
A	2	4		4	4
B	2	3	4	4	4
C	4	5	6	6	6
Total	8	12	10	10	10

The numbers in red symbolize it is wrong. It should be blank instead as Dec is blank for attribute A.

Hope it makes sense.

Br.,
Thomas

[thod]

Thank you all. Seems like it works with the datesmtd and I might have been overcomplicated things.

[Ashish_Mathur]

Hi,

Try these measures

Total = SUM( 'FactTable'[Column1] )

Last date of last selected month = calculate(max('DateTable'[DW_SK_Date]),allselected('DateTable'[Month name]))

First date of last selected month = eomonth([Last date of last selected month],-1)+1

Total in last month = calculate([total],datesbetween('DateTable'[DW_SK_Date],[First date of last selected month],[Last date of last selected month]))

Measure=if(isinscope('DateTable'[Month name]),[Total],[Total in last month])

Hope this helps.

[v-jingzhang]

Hi @thod 

Do you have "blank" data rows for attribute A with max date in December in the fact table? If so, your measure should work. If the max date for attribute A in the fact table is in November, that's why your measure doesn't work. 

Scenario One:

Scenario Two:

To get the expected result, you can use the following measure. I attached a pbix file at bottom which has both examples. 

New Measure = 
VAR MaxDate = MAX ( 'FactTable (2)'[Date] )
VAR MaxDate2 =
    IF (
        YEAR ( MaxDate ) = MAX ( 'DateTable'[Year] )
            && MONTH ( MaxDate ) = MAX ( 'DateTable'[Month] ),
        MaxDate,
        MAX ( 'DateTable'[Date] )
    )
VAR Result =
    CALCULATE ( SUM ( 'FactTable (2)'[Column1] ), 'DateTable'[Date] = MaxDate2 )
RETURN
    Result

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak · Accepted solution]

@thod , With help date table create mtd measure use month and slicer from date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Time Intelligence, Part of learning Power BI- https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...