Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have two tables, CALENDAR TABLE and the Fact Table.
In Fact Table I have TWO date columns, let’s call FT[date1] and FT[date2].
Both tables are connected. Calendar[Date] à FT[date1] (picture).
My main measure is simple:
volume m3s = SUM('Outorgas validas 2'[Volume m3s])
There is a “catch”. Speaking of 2018, I can only consider the values which are:
FT[date1] >= 01/01/2018 (DD, MM, YYYY)
FT[date2] <=31/12/2017
To make it clear, date1 is something like “expiring date”; and date 2 is “publishing date”.
What I want:
A dynamic FILTER, with the Calendar[Date] in a slicer. When I choose any date or interval, it dynamically apply the filters to calculate, calculating within the conditions. Let’s say I chose “15/01/2007”:
FT[date1] >= 15/01/2007 (DD, MM, YYYY)
FT[date2] <=14/01/2007
What I have thought:
Result =
VAR SELECTED = SELECTEDVALUE(dCalendar[Date])
RETURN
CALCULATE([volume m3s];
FILTER('Outorgas validas 2';
FT[date1] >= 15/01/2007 >= SELECTED &&
FT[date2] <=14/01/2007 <= SELECTED)
***
But I wasn’t able to get the correct results…
Thanks in advance!
(english is not my native language)
Solved! Go to Solution.
Hi @brunofs123,
To create a new calculated table and do not create relationship for the new table with other ones.
_newCalendar = CALCULATETABLE('dCalendar')
Then we can add the date column in the new table in slicer and create a measure as below.
out valid DIN 2 = VAR SELECTED = SELECTEDVALUE('_newCalendar'[Date]) RETURN CALCULATE(SUM('FT - Outorgas validas 2'[Volume m3s]), FILTER('FT - Outorgas validas 2' , 'FT - Outorgas validas 2'[date venc] >= SELECTED && 'FT - Outorgas validas 2'[date public] <= SELECTED) )
Please refer to the pbix.
Regards,
Frank
Hi @brunofs123,
Here we can create another calculated table and add the date column of the new table as slicer.
_New = Values(Calendar[Date])
And update your measure as below.
Result = VAR SELECTED = SELECTEDVALUE(_New[Date]) RETURN CALCULATE([volume m3s]; FILTER('Outorgas validas 2'; FT[date1] >= 15/01/2007 >= SELECTED && FT[date2] <=14/01/2007 <= SELECTED)
If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.
Regards,
Frank
Thank you for replying. I was only able to see it today, but unfortunately it didn't work.
Here is lhe one drive link:
I'm afraid i post a wrong calculation in my orginal message:
esult =
VAR SELECTED = SELECTEDVALUE(dCalendar[Date])
RETURN
CALCULATE([volume m3s];
FILTER('Outorgas validas 2';
FT[date1] >= 15/01/2007 >= SELECTED &&
FT[date2] <=14/01/2007 <= SELECTED)
please desconsider the red lighted characters
thanks!
Hi @brunofs123,
To create a new calculated table and do not create relationship for the new table with other ones.
_newCalendar = CALCULATETABLE('dCalendar')
Then we can add the date column in the new table in slicer and create a measure as below.
out valid DIN 2 = VAR SELECTED = SELECTEDVALUE('_newCalendar'[Date]) RETURN CALCULATE(SUM('FT - Outorgas validas 2'[Volume m3s]), FILTER('FT - Outorgas validas 2' , 'FT - Outorgas validas 2'[date venc] >= SELECTED && 'FT - Outorgas validas 2'[date public] <= SELECTED) )
Please refer to the pbix.
Regards,
Frank
Thank you!
Mind explaining why you had to create a new and unrelated Calendar?
Thanks..
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |