Forum Discussion

rschaudhr's avatar
rschaudhr
Icon for Resolver II rankResolver II
5 years ago
Solved

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.       So I need to show three columns. The first one is sum of sales based on first date, the second is sum ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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:

    For the  sample .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

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:

For the  sample .pbix file,pls see attached.

 

Best Regards,
Kelly

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

rschaudhr's avatar
rschaudhr
Icon for Resolver II rankResolver II
5 years ago

Thanks! It is working as required.