Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!

Thanks! It is working as required.

rschaudhr
Resolver II
Resolver II

I could not attach the dummy data with this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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