Forum Discussion

Bastaba's avatar
Bastaba
Frequent Visitor
7 years ago
Solved

Parameter Personnalized Column

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(FBL...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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.

    Parameter-Personnalized-Column

     

    Best Regards,
    Dale

Bastaba's avatar
Bastaba
Frequent Visitor
7 years ago

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,

 

 

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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.

Parameter-Personnalized-Column

 

Best Regards,
Dale

  • Bastaba's avatar
    Bastaba
    Frequent Visitor
    7 years ago

    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 :

    GROUP_DATA =
    IF(DATEDIFF('Date'[Date];TODAY();Month)>2;"<M-2";
    IF(DATEDIFF('Date'[Date];TODAY();Month)=2;"M-2";
    IF(DATEDIFF('Date'[Date];TODAY();Month)=1;"M-1";
    IF(DATEDIFF('Date'[Date];TODAY();Day)>0;"- M";
    IF(AND(DATEDIFF('Date'[Date];TODAY();Day)<=0;DATEDIFF('Date'[Date];TODAY();MONTH)=0);"+M";
    ">M"
    )))))
     
    i should change the others formulas by :
    DATEADD('Date'[Date].[Date]; -day_offset ; DAY)
    and
    day_offset = DATEDIFF(SELECTEDVALUE('Table_Paramètre_Date_Jour'[Date];TODAY()); TODAY(); DAY)
     
     
    Thx again for your answer !
     
    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      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,

      • Bastaba's avatar
        Bastaba
        Frequent Visitor
        7 years ago

        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 :

        IF(DATEDIFF(FBL5N[Date d'échéance];TODAY();Day)>0;"- M";
        IF(AND(DATEDIFF(FBL5N[Date d'échéance];TODAY();Day)<=0;DATEDIFF(FBL5N[Date d'échéance];TODAY();MONTH)=0);"+M";
        There will be 10 days with "-M"
         
        So if SELECTEDVALUE(Days[Date]) = 05/01/19
        31/12/19
        30/12/19
        ...
        26/12/19
        will be in  "-M" group and not in the "M-1" group
         
        because as you said GROUP_DATA can't be dynamic.

        What do i have to change please ?