Forum Discussion

Zyg_D's avatar
Zyg_D
Continued Contributor
5 years ago
Solved

Subtracting data from predefined base date

This measure is expected for every date (6 of them) to take SUM(dfMain[a3_invoiced units]) and subtract the same from DATE(2020,9,21) . In the graph I see that the subtraction only happens on the first date (result = 0) . How to make the subtraction work for other dates?

 

 

_m_A3_growth_cumul = 
VAR _base = DATE (2020,9,21)
VAR _a3_sum_base = SUMX ( FILTER ( dfMain,dfMain[Date] = _base ),
                          dfMain[a3_invoiced units] )
VAR _a3_sum = SUM(dfMain[a3_invoiced units])
VAR _a3_diff = _a3_sum - _a3_sum_base
RETURN _a3_diff

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Zyg_D ,

     

    Please refer to the following measure:

     

     

    _m_A3_growth_cumul = 
VAR _base = DATE (2020,9,21)
VAR _a3_sum_base = SUMX ( FILTER ( all(dfMain),dfMain[Date] = _base ),
                          dfMain[a3_invoiced units] )
VAR _a3_sum = SUM(dfMain[a3_invoiced units])
VAR _a3_diff = _a3_sum - _a3_sum_base
RETURN IF( ISBLANK(_a3_sum),BLANK(),_a3_diff)

     

     

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

     

    Best Regards,

    Dedmon Dai

     

5 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Zyg_D ,

     

    Please refer to the following measure:

     

     

    _m_A3_growth_cumul = 
VAR _base = DATE (2020,9,21)
VAR _a3_sum_base = SUMX ( FILTER ( all(dfMain),dfMain[Date] = _base ),
                          dfMain[a3_invoiced units] )
VAR _a3_sum = SUM(dfMain[a3_invoiced units])
VAR _a3_diff = _a3_sum - _a3_sum_base
RETURN IF( ISBLANK(_a3_sum),BLANK(),_a3_diff)

     

     

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

     

    Best Regards,

    Dedmon Dai

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Zyg_D ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Zyg_D ,

     

    Would you please try to use the following measure:

     

     

    _m_A3_growth_cumul = 
VAR _base = DATE (2020,9,21)
VAR _a3_sum_base = SUMX ( FILTER ( ALL(dfMain),dfMain[Date] = _base ),
                          dfMain[a3_invoiced units] )
VAR _a3_sum = SUM(dfMain[a3_invoiced units])
VAR _a3_diff = _a3_sum - _a3_sum_base
RETURN _a3_diff

     

     

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

     

    Best Regards,

    Dedmon Dai

    • Zyg_D's avatar
      Zyg_D
      Continued Contributor
      5 years ago

      Thanks, Dedmon Dai ( v-deddai1-msft ).

      Now subtraction seems to be working, However it also gives me all the dates from the Dates table. Any idea how I can keep only the 6 dates for which I have data? Or limit the subtraction to not be applied to all of the dates?