Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all I have a simply data model with a Calendar Table and a Fact Table with Date From and Date To fields.
Fact
Fact
Customer | DateStart | DateEnd | Operations |
A | 01/01/2018 | 07/01/2018 | 5 |
B | 05/01/2018 | 15/01/2018 | 2 |
C | 10/01/2018 | 20/01/2018 | 3 |
Since there is more than date 1 field (and joining tables is not trivial) on my fact table,
I have following requirement.
I require having a slicer to filter a measure to which displays my measure Sum Operations = Sum('Fact'[Operations]) having following requirement
Date Start (Fact) <= Date Selected (Calendar)<= Date End (Fact)
For example a table my new measure should be taken into account date fields
Date (Calendar) | new_measure |
01/01/2018 | 5 |
02/01/2018 | 5 |
03/01/2018 | 5 |
04/01/2018 | 5 |
05/01/2018 | 5+2 =7 |
06/01/2018 | 5+2 =7 |
07/01/2018 | 5+2 =7 |
08/01/2018 | 2 |
09/01/2018 | 2 |
10/01/2018 | 2+3=5 |
11/01/2018 | 2+3=5 |
12/01/2018 | 2+3=5 |
13/01/2018 | 2+3=5 |
14/01/2018 | 2+3=5 |
15/01/2018 | 2+3=5 |
16/01/2018 | 3 |
17/01/2018 | 3 |
18/01/2018 | 3 |
19/01/2018 | 3 |
20/01/2018 | 3 |
21/01/2018 | 0 |
Taken into account I am using Direct Query, how can I manage to work with this double date data.
Regards
Solved! Go to Solution.
Hi @dpombal,
Based on my test, you could add two calculated columns in the calender table:
Column = RELATED('Fact'[Customer])
New = VAR LastNonBlankDate = CALCULATE ( LASTNONBLANK ( 'Table'[Date], 1 ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) && NOT ( ISBLANK ( 'Table'[Column]) ) ) ) RETURN CALCULATE ( MAX( 'Table'[Column] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate ) )
Result:
You could also download the pbix file to have a view:
Regards,
Daniel He
Hi @dpombal,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
You can bring 2 Calender table, one for ToDate and the other for FromDate. Manage the Relationship and connect the 3 Tables(1 Fact, 2 Calender Table) as shown below.
Now In the report drop 2 slicer, 1 Date Will come from one Calender Table and rest from the other.
One Slicer Property will be:-
Before and the other will be After.
Using double Calendar Table is a good feature, however...how can I use an unique table of calendar, just thinking in other Fact tables with a Single Date Field . I would require to filter from an unique Calendar Table
Any suggestions
Hi @dpombal,
Based on my test, you could try to this formula:
Measure = IF ( ISBLANK ( CALCULATE ( SUM ( 'Fact'[Operations] ), FILTER ( ALL ( 'Fact' ), 'Fact'[DateStart] <= MAX ( 'Table'[Date] ) && 'Fact'[DateEnd] >= MAX ( ( 'Table'[Date] ) ) ) ) ), 0, CALCULATE ( SUM ( 'Fact'[Operations] ), FILTER ( ALL ( 'Fact' ), 'Fact'[DateStart] <= MAX ( 'Table'[Date] ) && 'Fact'[DateEnd] >= MAX ( ( 'Table'[Date] ) ) ) ) )
Result:
You could also download the pbix file to have a view:
Regards,
Daniel He
After adding customer as filter on the report this measure fails
Check report pbix here
Hi @dpombal,
Based on my test, you could add two calculated columns in the calender table:
Column = RELATED('Fact'[Customer])
New = VAR LastNonBlankDate = CALCULATE ( LASTNONBLANK ( 'Table'[Date], 1 ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) && NOT ( ISBLANK ( 'Table'[Column]) ) ) ) RETURN CALCULATE ( MAX( 'Table'[Column] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate ) )
Result:
You could also download the pbix file to have a view:
Regards,
Daniel He
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |