Solved: Cumulative total over 2 years - Page 2

pawelj795 · ‎01-14-2020

Hi,
I have quick question.

Is it possible to create measure which will sum cumulatevily values over 2 years?

I mean it must be something like YTD, but for 2 years, not only one year.

My current measure looks like this:

Spoiler

YTD Revenues =
CALCULATE(
SUM(
Invent_Trans (Inventory Value);
DATESYTD(DimDates(Date))

My problem arise when 2020 start, so my cumulative Revenues are approximately 0, but I need to sum them with previous year cumulatevily.

Icey · ‎01-15-2020

Hi @pawelj795 ,

First, one step in the above formula was complicated by me. I have modified it. Please check:

YTD Revenues 5 = 
VAR LastYearFirstDate =
    IF (
        SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
        DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 ),
        DATE ( SELECTEDVALUE ( 'Calendar'[Year] ) - 1, 1, 1 ) -------------changed
    )
VAR CurrentDate =
    IF (
        SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
        MAX ( DimDates[Date] ),
        CALCULATE (
            MAX ( DimDates[Date] ),
            FILTER ( DimDates, DimDates[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) )
        )
    )
RETURN
    CALCULATE (
        SUM ( Invent_Trans[Inventory Value] ),
        FILTER (
            ALLSELECTED ( DimDates ),
            DimDates[Date] >= LastYearFirstDate
                && DimDates[Date] <= CurrentDate
        )
    )

Then, you can create your YearWeek column like so:

YearWeek =
CONCATENATE (
    Invent_Trans[Year],
    CONCATENATE ( "  ", FORMAT ( Invent_Trans[WeekNum], "00" ) )
)

Best Regards,

Icey

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

View solution in original post

Cumulative total over 2 years

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly