Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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!
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!
Thanks! It is working as required.
I could not attach the dummy data with this.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |