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 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:

 

 

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

 

 

 

I have attached dummy data witht this message.

 

 

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!

3 Replies

  • 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!