Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pawelj795
Post Prodigy
Post Prodigy

Cumulative total over 2 years

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.

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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" ) )
)

 yw.PNG

 

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

13 REPLIES 13
Anonymous
Not applicable

i have a very similar problem.
I have a dimensional table, containing the number of Stores, I want to have the cumulative sum per year of the number of active stores.

See example:

simker96_0-1641235090999.png


therefore the number of active stores in 2012 is given by the sum of the stores created in 2011 and 2012.
The number of stores active in 2013 will be given by the sum of the stores active in 2011 and 2012.

simker96_1-1641235276038.png


Can you help me to solve this problem?

Thank you

 

Icey
Community Support
Community Support

Hi @pawelj795 ,

 

Is this problem solved?

 

Best Regards,

Icey

@Icey 
@amitchandak 

I was just trying this solution, but it doesn't seem working.
Why you using EndOfMonth function? What is the purpose?

image.png

Icey
Community Support
Community Support

Hi @pawelj795 ,

 

I create a simple example. What about this?

YTD Revenues 3 = 
VAR LastYearFirstDate =
    DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) )  - 1, 1, 1 )
VAR CurrentDate =
    MAX ( DimDates[Date] )
RETURN
    CALCULATE (
        SUM ( Invent_Trans[Inventory Value] ),
        FILTER (
            ALLSELECTED ( DimDates ),
            DimDates[Date] >= LastYearFirstDate
                && DimDates[Date] <= CurrentDate
        )
    )

ytd2.PNG

For details, please check the attached PBIX file.

 

Best Regards,

Icey

 

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

@Icey 
It's working!

But I need small adjustment in my report.

I have slicers for years (2019/2020 or both) in whole page, so when I switch for only 2020, values are incorrect (which is valid)

Do you have any idea how to smartly create slicers that measure would work for 2020 and viewers of my report can also turn on 2020 for rest of report?

Icey
Community Support
Community Support

Hi @pawelj795 ,

 

In my scenario, it works well. Maybe I understand something wrong, please let me know. And if you don't mind, please share me a dummy PBIX file, without real data or sensitive information.

ytd3.gif

 

Best Regards,

Icey

@Icey 
Maybe, I explain it incorrectly.

image.png

I mean that when I select only 2020 in slicer, I want to have cumulative value from 2019 too.
image.png

 

To sum up, I want to choose only  2020 in slicer, but I want to have cumulative value from 2019 and 2020 too.
In your case, would be 119 for 2-1-2020.

Icey
Community Support
Community Support

Hi @pawelj795 ,

 

For "single select", ty this:

1. Create another Calendar table without relationships among others.

Calendar = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ) )

2. Create a measure.

YTD Revenues 4 = 
VAR LastYearFirstDate =
    IF (
        SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
        DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 ),
        DATE ( YEAR (
            CALCULATE (
                LASTDATE ( Invent_Trans[Date] ),
                FILTER ( DimDates, DimDates[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) )
            )
        ) - 1, 1, 1 )
    )
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
        )
    )

 3. Create a Year slicer from Calendar table.

ytd4.gif

 

Best Regards,

Icey

 

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

@Icey 
It's finally seems working 🙂

One question though, in my main date table I add column with combined year and week number.
yimage.png

How to sort them correctly?

Icey
Community Support
Community Support

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" ) )
)

 yw.PNG

 

Best Regards,

Icey

 

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

HI @Icey 
I need to dig up my topic.

Currently, I'm updating this report and I need to add slicer with quarters and week numbers, but it require some modification to your measure.

In current conditions, I can only add slicer with year from table "Calendar".


Can you help me?

That work's perfectly! 
Thanks

amitchandak
Super User
Super User

Try Like

 

Sales 2 Years = 
IF( 
    NOT ISEMPTY( Sales ), 
    CALCULATE(
        [Sales],
        DATESINPERIOD('DimDates'[Date],ENDOFMONTH(Sales[Sales Date]),-2,YEAR))
    )
)

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.