cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Delta calculation based on dynamic Slicer

Hi,

I'm stuck with this New Measure field and I'd like to seek your help. We're taking Salesforce snapshot data and want to show comparison of forecast based on different snapshot date. Which snapshot date to pick will be on a slicer.

My sample date per below:

 Country Forecast Snapshot Date A 1,000 Day 1 B 2,000 Day 1 C 1,000 Day 1 D 2,000 Day 1 E - Day 1 A 2,000 Day 2 B 5,000 Day 2 C - Day 2 D 2,000 Day 2 E 3,000 Day 2 A 1,500 Day 3 B 5,500 Day 3 C - Day 3 D 5,000 Day 3 E 1,000 Day 3

If user choose snapshot Day 1 and Day 2 in the Slicer, the result would be

 Snaphsot Date Day 1 Day 2 Forecast Delta A 1,000 2,000 1,000 B 2,000 5,000 3,000 C 1,000 - (1,000) D 2,000 2,000 - E - 3,000 3,000 6,000 12,000 6,000

If user choose snapshot Day 2 and Day 3 in the Slicer, the result would be

 Snaphsot Date Day 2 Day 3 Forecast Delta A 2,000 1,500 (500) B 5,000 5,500 500 C - - - D 2,000 5,000 3,000 E 3,000 1,000 (2,000) 12,000 13,000 1,000

Would you be able to advise on this? Thanks a lot!

1 ACCEPTED SOLUTION
Community Support

Hi @ilui ,

Based on your description, you can create this measure:

``````Forecast Diff =
VAR _max =
CALCULATE ( MAX ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _min =
CALCULATE ( MIN ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _maxsum =
CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _max )
VAR _minsum =
CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _min )
RETURN
_maxsum - _minsum``````

To achieve the same visual, create two matrix to use visual overlay:

Best Regards,
Yingjie Li

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

8 REPLIES 8
Community Support

Hi @ilui ,

Based on your description, you can create this measure:

``````Forecast Diff =
VAR _max =
CALCULATE ( MAX ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _min =
CALCULATE ( MIN ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _maxsum =
CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _max )
VAR _minsum =
CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _min )
RETURN
_maxsum - _minsum``````

To achieve the same visual, create two matrix to use visual overlay:

Best Regards,
Yingjie Li

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

Frequent Visitor

@v-yingjl Thanks heaps! The formula works well but for some reason, the Forecast Diff show up Twice. Look like below

I thought it's because of all the drilldown I have, but after removing the drilldown, it looks the same.

 2020017 20200810 Country Forecast Forecast Diff Forecast Forecast Diff A \$18,789,996 \$4,807,466 \$23,597,463 \$4,807,466 B \$12,156,438 \$2,350,168 \$14,506,605 \$2,350,168 C \$19,781,996 \$3,535,801 \$23,317,797 \$3,535,801 \$50,728,430 \$10,693,435 \$61,421,865 \$10,693,435

I downloaded your sample but I cannot open it. Error message says "Object reference not set to an instance of an object"

Community Support

Hi @ilui ,

This is because measure cannot be used as a separate column in the matrix, it is by design in it.

As I previous posted, you can create two matrix and superimpose them, set the date matrix arrange as 'Bring to front' in the Format pane, adjust the size of two matrix visuals properly.

I have re-uploaded the sample file that you can refer it, please try to open it with the latest version of power bi desktop (August 2020) because it is currently known that there is a problem that the lower version cannot be used to open the higher version file.

Sample file: Forecast.pbix

Best Regards,
Yingjie Li

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

Frequent Visitor

@v-yingjl Oh I missed the super imposed part. I'll try but I'm not sure if it'll work for my situation because my Matrix visual will have several level of drill down for people to look at the variances.

If measures cannot be used as a separate column in the matrix, any alternative suggestion on how to show this better?

Thanks a lot of your help!

Community Support

Hi @ilui ,

One alternative solution is to use combo charts like stacked column and line chart instead of matrix to show the diff obviously.

Best Regards,
Yingjie Li

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

Community Champion

You can do quite alot of workarounds in matrixes by using Selectedvalue() and blanking values.

Here's a brief example:

Plain thable just displaying the sum of cats. No conditions.

If we want to remove specific columns in the matrix we can look from them using SELECTEDVALUE and then remove them:

Measure replacing the Blue cats with blanks()

``````Count_ no Blue =
VAR Color_ = SELECTEDVALUE(Facts[Color])
return
IF( Color_ = "BLUE" , BLANK() , [Count_] )``````

Notice that the Total is still displaying 160, even through the total displayed sum is only 40.

Just specifiy the column and value your want to identify. Use a variable with selectedvalue, and then compare that variable in an IF statment:

Removing cat 3 and cat 5 from the matrix

``````Count_ no Cats =
VAR Category_ = SELECTEDVALUE(Facts[Category])
return
IF( OR( Category_  = "Cat 3" , Category_ = "Cat 5" ) , BLANK() , [Count_] )``````

Hope this helps!

Br,
J

Super User

@ilui , You can use day vs last day and take a diff. Use date table.

Example

``````Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf])  ),Table[Date]) ,Table[Date],Day)

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))``````

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Frequent Visitor

The snapshot date is not necessarily Day -1. The snapshot data is twice a week so the date can be

20200511

20200516

20200518 etc etc

also, are below a number of Measures? or 1 measure?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors