Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have setup a data model with a dynamic date selection slicer. It works great when I have my Calendar table connected directly to my Date Periods table. However, I need to also include Year over Year Sales Amount, which is where I'm stuck. I need to be able to select a date period (like Last Quarter), and get results by date as:
Sales = sum(amount) 10/1/21 - 12/31/21
Sales Last Year = sum(amount) 10/1/20 - 12/31/20
Here is a link to my sample file: sample.pbix
Results with date table connected:
Data model with connected date table:
With disconnected date table:
Data model (disconnected Date Periods and Calendar):
Solved! Go to Solution.
Hi, @smileamile2 ;
You could add another measure.
Measure = CALCULATE([Sales Last Year],PARALLELPERIOD('Sales'[sales_date],-12,MONTH),ALL('Sales'))
The final output is shown below:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @smileamile2 ;
You could add another measure.
Measure = CALCULATE([Sales Last Year],PARALLELPERIOD('Sales'[sales_date],-12,MONTH),ALL('Sales'))
The final output is shown below:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @smileamile2 ;
May be should delete relationship, then modify the measure as follows:
Sales = CALCULATE( SUM(Sales[amount]),FILTER('Sales',[sales_date] in VALUES('Date Periods'[Date])))
Sales Last Year = CALCULATE( SUM(Sales[amount]),FILTER('Sales',[sales_date] in DATEADD('Date Periods'[Date],-1,YEAR)))
The final output is shown below:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yalanwu-msft Hello! I very much appreciate your help on this. However, I think I incorrectly explained how I needed it to display. Your solution works well, but it attaches the individual dates to the current and last year measures. Instead, I need it to have them both attached to the "current" dates, but showing current and last year's amounts for the values.
Using the snapshot below as an example, suggested solution displays as the top chart. The bottom chart is what I need to get as the result.
FYI - I updated the sample file to include 2020 data too, so the sample set covers prior year for all date filter options.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |