CODEWITHSUNDEEP
powerbidaxdata-analysispowerbi-desktopmeasure
BigBen
BigBen

Reputation: 50161

YTD sum by month, using only latest value for each month

Given a daily fact table, I want to create a measure for YTD sum (by month), but only summing the last available observation for each month.

The layout looks like:

enter image description here

using the Date date table to select a particular month, the Date Copy table to display each month for the year up to the selected month, and a Pred value showing the last available prediction for each month (from the Prediction table).

The desired result is:

enter image description here

My attempt:

Bad = 
VAR LatestMonth =
    SELECTEDVALUE ( 'Date'[Month Number] )
VAR Tbl =
    SUMMARIZE (
        ALLSELECTED ( 'Date Copy' ),
        'Date Copy'[Month Number],
        'Date Copy'[Year],
        "Bad", [Pred]
    )
RETURN
    SUMX ( FILTER ( Tbl, [Month Number] <= LatestMonth ), [Pred] )

Which returns:

enter image description here

Code to reproduce the tables:

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "Day", DAY ( [Date] ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Month Year", FORMAT ( [Date], "mmm yyyy" ),
    "EOM", EOMONTH ( [Date], 0 )
)

Date Copy = 
SELECTCOLUMNS (
'Date',
"Date", 'Date'[Date],
"Year", 'Date'[Year],
"Month", 'Date'[Month],
"Month Number", 'Date'[Month Number]
)

Prediction = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 3, 28 ) ),
    "Prediction",
        (
            1000 * DAY ( EOMONTH ( [Date], 0 ) ) - 10
        )
)

and measures:

Pred = 
VAR Dates = DATESBETWEEN ( 'Date Copy'[Date] , STARTOFYEAR ( 'Date'[Date] ), MAX ( 'Date'[Date] ) )
RETURN 
    CALCULATE (
        LASTNONBLANKVALUE ( 'Date Copy'[Date], SUM ('Prediction'[Prediction] ) ),
        REMOVEFILTERS ( 'Date' ),
        USERELATIONSHIP ( 'Prediction'[Date], 'Date Copy'[Date] ),
        KEEPFILTERS ( Dates )
    )

Upvotes: 1

Views: 101

Answers (1)

davidebacci
davidebacci

Reputation: 30324

I'm not sure I understand your setup but this works.

enter image description here

enter image description here

Bad = 
VAR LatestMonth =
    SELECTEDVALUE ( 'Date'[Month Number] )
VAR LatestYear =
    SELECTEDVALUE ( 'Date'[Year] )    
VAR Tbl =
    SUMMARIZE (
        ALLSELECTED('Date Copy') ,
        'Date Copy'[Month Number],
        'Date Copy'[Year],
        "Bad", [Pred]
    )

RETURN
CALCULATE(
    SUMX(FILTER(Tbl, 'Date Copy'[Month Number] <= SELECTEDVALUE('Date Copy'[Month Number]) ),[Bad]),
    'Date Copy'[Month Number] <= LatestMonth
)

Upvotes: 1

Related Questions