Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I created a personnalized column in order to creat 4 group of my column 'Date'.
Formula :
GROUP_DATA =
IF(DATEDIFF(FBL5N[Date d'échéance];TODAY();Month)>2;"<M-2";IF(DATEDIFF(FBL5N[Date d'échéance];TODAY();Month)=2;"M-2";IF(DATEDIFF(FBL5N[Date d'échéance];TODAY();Month)=1;"M-1";IF(DATEDIFF(FBL5N[Date d'échéance];TODAY();Month)=0;"M";">M"))))
So my data is classified according to the date of the day.
I would like to switch the day's date to a variable in order to change my visualization in my report.
I know i can do that through parameter in the 'data' part but i want to do that easily in my 'report' part.
I tried to creat a measure :
Day_Selected = SELECTEDVALUE('Table_Paramètre_Date_Jour'[Date];TODAy())
and to put my variable in the GROUP DATA's formula but it didn't work.
COuld You help me please ?
Regards ,
Solved! Go to Solution.
Hi @Bastaba,
Yes, it has to be a column that provides the context while a measure can't.
I have created a workaround. Please download it from the attachment.
1. Create an independent table [Days].
2. Create a measure.
Measure = VAR month_offset = DATEDIFF ( SELECTEDVALUE ( Days[Date] ), TODAY (), MONTH ) RETURN CALCULATE ( SUM ( 'Table_Paramètre_Date_Jour'[Value] ), DATEADD ( FBL5N[Date d'échéance], - month_offset, MONTH ) )
3. Please refer to the snapshot below.
Best Regards,
Dale
Hi @Bastaba,
I'm afraid we can't do it like that. The GROUP_DATA is a calculated column that can't respond to the slicer. How would you like to change the visualization? Can you share sample data, please?
It seems the GROUP_DATA could be in the X-axis. The workaround could be making the value dynamic.
Best Regards,
Dale
Hi,
Yes this is actually in the X-Axis.
I just want to change my filter and so the day-selected variable change the X-axis.
Here, some pictures to illustrate what i want :
I know how to make this works for a measure but not for a personnalized column.
But if i want to put it in my X-axis, it has to be a Column and not a measure, isn'it ?
Thx,
Hi @Bastaba,
Yes, it has to be a column that provides the context while a measure can't.
I have created a workaround. Please download it from the attachment.
1. Create an independent table [Days].
2. Create a measure.
Measure = VAR month_offset = DATEDIFF ( SELECTEDVALUE ( Days[Date] ), TODAY (), MONTH ) RETURN CALCULATE ( SUM ( 'Table_Paramètre_Date_Jour'[Value] ), DATEADD ( FBL5N[Date d'échéance], - month_offset, MONTH ) )
3. Please refer to the snapshot below.
Best Regards,
Dale
Hi !
I am answering only today because i wanted to understand perfectly your answer.
Thanks a lot ! I haven't mastered this database skill.
Just a question, how can i use this if i would like to do the same thing but on each day.
Because in a month, there 30 ou 31 days. So your formula doesn't work, does it ?
For example, if i change the GROUP_DATA formula by this formula :
Hi @Bastaba,
It's my pleasure.
Since the [GROUP_DATA] can't be dynamic, we leave it alone and change the result. For example, if Today is M and 2019-01-01 is the new M, the interval is fixed. Today to 2019-01-01 and Yesterday to 2018-12-31. So we use this offset to change the result.
You almost got the solution. I think this one should work.
Measure = VAR month_offset = DATEDIFF ( SELECTEDVALUE ( Days[Date] ), TODAY (), day) RETURN CALCULATE ( SUM ( 'Table_Paramètre_Date_Jour'[Value] ), DATEADD ( FBL5N[Date d'échéance], - month_offset, day ) )
One tip, if we put Today and result together, there could be a confusion. Because the M is 2019-01-01 rather than Today.
Best Regards,
Yes this is it.
But if TODAY = 11/01/19
There will be 10 days to the beginning of the month in GROUP_DATA
SO with this formula :
Hi @Bastaba,
Did you also change the rules in the [GROUP_DATA]? If not, "-M" should have a range of one month. So yes, 26/12/19 is in "-M".
Best Regards,
What formula do i have to write for GROUP_DATA please ?
Regards,
Maybe this one?
GROUP_DATA = IF ( DATEDIFF ( 'Date'[Date]; TODAY (); DAY ) > 30; "<D-20"; IF ( DATEDIFF ( 'Date'[Date]; TODAY (); DAY ) >= 20; "D-20"; IF ( DATEDIFF ( 'Date'[Date]; TODAY (); DAY ) >= 10; "D-10"; IF ( DATEDIFF ( 'Date'[Date]; TODAY (); DAY ) > 0; "-D"; IF ( DATEDIFF ( 'Date'[Date]; TODAY (); DAY ) = 0; "D"; ">D" ) ) ) ) )
Best Regards,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |