Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Zyg_D
Continued Contributor
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

 

 

 Capture.PNG

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
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

 

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
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

 

v-deddai1-msft
Community Support
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

Zyg_D
Continued Contributor
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? 

Capture.PNG

amitchandak
Super User
Super User

@Zyg_D , We do not have access to file.

amitchandak
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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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