CODEWITHSUNDEEP
powerbidax
rane
rane

Reputation: 931

Current YTD vs Last Month YTD Power BI Measure

Goal:

Data :

ind YEAR    MON Figures
1   2020    JAN 12321
2   2020    FEB 344
3   2020    MAR 5000
4   2020    APR 325
5   2020    MAY 747
1   2019    JAN 54091
2   2019    FEB 349
3   2019    MAR 789
4   2019    APR 901
5   2019    MAY 2356

Desire Result:

Usage - Default filter as newest year = 2020 , user can select the specific single year to view the change in scorecard

ind YEAR    MON Figures YTD     Change
1   2020    JAN 12321   12321   0
2   2020    FEB 344     12665   344
3   2020    MAR 5000    17665   5000
4   2020    APR 325     17990   325
5   2020    MAY 747     18737   747
1   2019    JAN 54091   54091   0
2   2019    FEB 349     54440   349
3   2019    MAR 789     55229   789
4   2019    APR 901     56130   901
5   2019    MAY 2356    58486   2356

How can i achieve this ?

Upvotes: 1

Views: 1769

Answers (2)

sergiom
sergiom

Reputation: 4877

If you don't need aggregated results, and assuming that Ind column is sorted by month, restarting each year, like in the sample data, then you might consider to create two calculated columns. Here I assume the table's name is "Tab"

YTD that accumulates the Figures value for the year of the current column up to the current month included

YTD =
VAR CurrentYear = Tab[YEAR]
VAR CurrentIndex = Tab[ind]
RETURN
    SUMX(
        FILTER(
            Tab,
            Tab[Year] = CurrentYear
                && Tab[Ind] <= CurrentIndex
        ),
        Tab[Figures]
    )

and Change, that at the row level is Just Figures unless the month i January, where its value is 0

Change = 
IF( Tab[Ind] = 1, 0, Tab[Figures] )

Then you can use these column in a table with YEAR and MON columns to get the correct level of granularity.

If you just need the last value for the currently selected year, like to be used in a card, then you can implement a measure to get the Last Change

Last Change =
VAR LastYear =
    MAX( Tab[YEAR] )
VAR LastIndex =
    CALCULATE(
        MAX( Tab[Ind] ),
        Tab[YEAR] = LastYear
    )
RETURN
    CALCULATE(
        VALUES( Tab[Change] ),
        Tab[Year] = LastYear,
        Tab[ind] = LastIndex
    )

Upvotes: 1

msta42a
msta42a

Reputation: 3741

Do you have relationship to calendar? Or in your table is column for MonthNo? if yes then te simplest way to calculate YTD:

YTD = CALCULATE (
        SUM ( 'Table'[Figures] ),
        FILTER (
            ALL ( 'Calendar'[Year], 'Calendar'[MonthNo] ),
            'Calendar'[Year] = SELECTEDVALUE ( 'Calendar'[Year] )
                && 'Calendar'[MonthNo] <= SELECTEDVALUE ( 'Calendar'[MonthNo] )
        )
    )

Here we sum [Figures] column for the current year (from table context), from 1 month to X month (current for row).

YTD_LY = CALCULATE (
        SUM ( 'Table'[Figures] ),
        FILTER (
            ALL ( 'Calendar'[Year], 'Calendar'[MonthNo] ),
            'Calendar'[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) -1 //add -1 here
                && 'Calendar'[MonthNo] <= SELECTEDVALUE ( 'Calendar'[MonthNo] )
        )
    )

Upvotes: 1

Related Questions