cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rschaudhr
Resolver II
Resolver II

Dax Calculation selection from slicer min value show wrong info (modified)

I have a question. I have a date filter and users are going to select two dates.

 

rschaudhr_3-1623368703328.png

 

 

So I need to show three columns. The first one is sum of sales based on first date, the second is sum of sales based on second date and the last one is based on the difference between the first date and second date.

 

So, the I want to see a column visual as follows:

 

rschaudhr_4-1623368703329.png

 

The caveat is that Date1 or Dat2 values per L3_Port_Code Category should be based on the actual dates selected.  For example on 12/31/2020 there is no value for AIMERGE, the Date 1 column should show 0

 

rschaudhr_5-1623368703330.png

 

 

I have attached dummy data witht this message.

 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @rschaudhr ,

 

First create a Calendar table based on your date as the slicer;

Then create 3 measures as below:

Date 1 = 
var _date=CALCULATE(MIN('calendar table'[Date]),ALLSELECTED('calendar table'))
Return
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[L3_Port_code]=MAX('Table'[L3_Port_code])&&'Table'[Date]=_date))
Date 2 = 
var _date=CALCULATE(MAX('calendar table'[Date]),ALLSELECTED('calendar table'))
Return
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[L3_Port_code]=MAX('Table'[L3_Port_code])&&'Table'[Date]=_date))
Change = 'Table'[Date 2]-'Table'[Date 1]

And you will see:

v-kelly-msft_0-1623660761344.png

For the  sample .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi  @rschaudhr ,

 

First create a Calendar table based on your date as the slicer;

Then create 3 measures as below:

Date 1 = 
var _date=CALCULATE(MIN('calendar table'[Date]),ALLSELECTED('calendar table'))
Return
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[L3_Port_code]=MAX('Table'[L3_Port_code])&&'Table'[Date]=_date))
Date 2 = 
var _date=CALCULATE(MAX('calendar table'[Date]),ALLSELECTED('calendar table'))
Return
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[L3_Port_code]=MAX('Table'[L3_Port_code])&&'Table'[Date]=_date))
Change = 'Table'[Date 2]-'Table'[Date 1]

And you will see:

v-kelly-msft_0-1623660761344.png

For the  sample .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

Thanks! It is working as required.

rschaudhr
Resolver II
Resolver II

I could not attach the dummy data with this.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!