cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Continued Contributor

## 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``````

1 ACCEPTED SOLUTION
Community Support

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 5
Community Support

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

Community Support

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

Continued Contributor

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?

Super User

Super User

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors