Calculate Sales until the Selected Year (row by row)

[RoshanK]

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!

[tamerj1]

@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] ) ) )

[RoshanK]

@tamerj1 

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

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

[tamerj1]

@RoshanK 

Yes. Sorry for the typo. 

[RoshanK]

@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?

[tamerj1]

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

[RoshanK]

@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.

[tamerj1]

@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?

[RoshanK]

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

[tamerj1 · Accepted solution]

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

[RoshanK]

This works like magic. I learned a cool technique.

Thank you very much @tamerj1 !