Forum Discussion

DianeUmu's avatar
DianeUmu
Regular Visitor
2 years ago
Solved

DAX Formula

Hello,

I'm using the above DAX to get selected month values and same period last year value of the selected date and I want now to calculate the difference between the 2. Below is the result from above DAX.

I need to calculate difference between the two dates, 11/30/2023 being selected date and 11/22/2022 being start date.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  DianeUmu ,

     

    You can try the following Dax.

    Test =
var _mindate=
IF(
    [SMRR Gain]=BLANK(),BLANK(),
MINX(FILTER(ALLSELECTED('vm_fct_store_month_360'),[SMRR Gain]<>BLANK()),[LAST_CALENDAR_DATE]))
var _maxdate=
IF(
    [SMRR Gain]=BLANK(),BLANK(),
MAXX(FILTER(ALLSELECTED('vm_fct_store_month_360'),[SMRR Gain]<>BLANK()),[LAST_CALENDAR_DATE]))
return
SUMX(    FILTER(ALLSELECTED('vm_fct_store_month_360'),'vm_fct_store_month_360'[LAST_CALENDAR_DATE]=_mindate),[SMRR Gain])
-
SUMX( FILTER(ALLSELECTED('vm_fct_store_month_360'),'vm_fct_store_month_360'[LAST_CALENDAR_DATE]=_maxdate),[SMRR Gain])

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  DianeUmu ,

     

    You can try the following Dax.

    Test =
var _mindate=
IF(
    [SMRR Gain]=BLANK(),BLANK(),
MINX(FILTER(ALLSELECTED('vm_fct_store_month_360'),[SMRR Gain]<>BLANK()),[LAST_CALENDAR_DATE]))
var _maxdate=
IF(
    [SMRR Gain]=BLANK(),BLANK(),
MAXX(FILTER(ALLSELECTED('vm_fct_store_month_360'),[SMRR Gain]<>BLANK()),[LAST_CALENDAR_DATE]))
return
SUMX(    FILTER(ALLSELECTED('vm_fct_store_month_360'),'vm_fct_store_month_360'[LAST_CALENDAR_DATE]=_mindate),[SMRR Gain])
-
SUMX( FILTER(ALLSELECTED('vm_fct_store_month_360'),'vm_fct_store_month_360'[LAST_CALENDAR_DATE]=_maxdate),[SMRR Gain])

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • DianeUmu's avatar
      DianeUmu
      Regular Visitor
      2 years ago

      Liu, The formula worked, However I want to have the difference between the two dates in the same measure and the output should look like this

      Basically have differenece value next to 11/30/2023.
      Please advice.

      • DianeUmu's avatar
        DianeUmu
        Regular Visitor
        2 years ago

        And using the formula that you provided, It's giving me the same amount for the 2 dates without the difference.