topic Re: Calculate Sales until the Selected Year (row by row) in DAX Commands and Tips

Calculate Sales until the Selected Year (row by row)

RoshanK — Sun, 26 Feb 2023 16:36:44 GMT

Hi,

I've sales & calendar table. Simple data model. Table for reference.

What I am trying to do is ; When a user selects Year, sales until that Year is shown.

For example, if 2020 is selected then Sales for 2018,2019, and 2020 is shown.

Any help/guidance will be appreciated. Thanks in advance!

Re: Calculate Sales until the Selected Year (row by row)

tamerj1 — Sun, 26 Feb 2023 10:52:57 GMT

RoshanK
Create a disconnected Year table like

Year = -- calculated table ALLNOBLANKROW ( 'Date'[Year] )

Make sure this table is completely disconnected from any other table in the model.

Then the measure would be

Acummulated Liters = CALCULATE ( [Liters], KEEPFILTERS ( 'Date'[Liters] <= MAX ( Year[Year] ) ) )

Re: Calculate Sales until the Selected Year (row by row)

RoshanK — Sun, 26 Feb 2023 19:29:06 GMT

tamerj1

Acummulated Liters =
CALCULATE ( [Liters], KEEPFILTERS ( 'Date'[Liters] <= MAX ( Year[Year] ) ) )

In this measure, did you mean Date[Year] <= Max(Year[Year])?

Re: Calculate Sales until the Selected Year (row by row)

tamerj1 — Mon, 27 Feb 2023 02:19:55 GMT

RoshanK

Yes. Sorry for the typo.

Re: Calculate Sales until the Selected Year (row by row)

RoshanK — Mon, 27 Feb 2023 07:01:37 GMT

tamerj1

I created disconnected table, like you suggested.

Here is the result from the measure you suggested;

First Challenge: Accumulated Liters is cummulative liters. What I was looking for is Liters for selected year & month.

Second Challenge: If I filter using Disconnected Year, it doesn't work. For example: when Year 2022 is selected, I still want sales for 2018,2019,2020,&2021 (row by row). Not the result below:

Any suggestion?

Re: Calculate Sales until the Selected Year (row by row)

tamerj1 — Mon, 27 Feb 2023 07:26:13 GMT

Hi RoshanK
The Year from the disconnected table will be used only in slicer not in the table visual. Tis table doesn't habe to be a complete Date table, rather it can be only a single column table that contains 5 rows (the values of the 5 avialable years) you can use the code in my original reply to create it or just insert manually.

In the table visual you can use the year column from your connected calendar table and the code can be

Acummulated Liters = CALCULATE ( [Liters], KEEPFILTERS ( 'Calendar'[Years] <= MAX ( 'Year'[Year] ) ) )

Re: Calculate Sales until the Selected Year (row by row)

RoshanK — Mon, 27 Feb 2023 14:55:05 GMT

tamerj1

I used exactly the same logic you suggested. For some reasons, it is not working!

In column: Year from Calendar & Accumulated Liters.

Year Slicer from disconnected table created using ALLNOBLANKROW()

Thanks in advance! In the meantime I will be exploring other logics to solve this challenge.

Re: Calculate Sales until the Selected Year (row by row)

tamerj1 — Mon, 27 Feb 2023 15:35:36 GMT

RoshanK
Are you sure the year column used in the table is from 'Calendar' table and that the 'Calendar' table is connected to the fact table?

Re: Calculate Sales until the Selected Year (row by row)

RoshanK — Mon, 27 Feb 2023 16:03:18 GMT

Yes. Take a look here ( Calendar, Fact table, & Disconnected table)!

Re: Calculate Sales until the Selected Year (row by row)

tamerj1 — Mon, 27 Feb 2023 17:07:05 GMT

RoshanK

Yes, you are right. Please refer to attached sample file

Acummulated Liters = VAR CalendarYear = MAX ( 'Calendar'[Year] ) VAR YearYear = MAX ( 'Year'[Year] ) RETURN IF ( CalendarYear <= YearYear, CALCULATE ( [Total Liters], 'Calendar'[Year] <= MIN ( CalendarYear, YearYear ) ) )

Re: Calculate Sales until the Selected Year (row by row)

RoshanK — Mon, 27 Feb 2023 17:29:10 GMT

This works like magic. I learned a cool technique.

Thank you very much tamerj1 !