Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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
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] ) ) )
Acummulated Liters = CALCULATE ( [Liters], KEEPFILTERS ( 'Date'[Liters] <= MAX ( Year[Year] ) ) )
In this measure, did you mean Date[Year] <= Max(Year[Year])?
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?
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] ) ) )
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.
@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?
Yes. Take a look here ( Calendar, Fact table, & Disconnected table)!
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 )
)
)
User | Count |
---|---|
53 | |
22 | |
18 | |
18 | |
13 |
User | Count |
---|---|
89 | |
87 | |
50 | |
34 | |
22 |