Subtracting data from predefined base date

[Zyg_D]

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

 

[v-deddai1-msft · Accepted solution]

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

[v-deddai1-msft]

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]

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? 

[amitchandak]

@Zyg_D , We do not have access to file.

[amitchandak]

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