Forum Discussion
QTY based on the date range
Hello, I would like to have a table where 1 column show total quantity and the other sum of QTY based on the date range that can be selected. How to do it?
hi Ania26
I added more rows to your sample table.
First, create a separate dates/calendar table and create a one to many single direction relationship from that to your fact table joining on their respective date columns.
Create either of these measures that modify the filter context on DatesTable:
Qty 2023-2024 = //total qty for 2023-24 regardless of the date range selected CALCULATE ( SUM ( 'DataTable'[QTY] ), FILTER ( ALL ( DatesTable ), DatesTable[Year] IN { 2023, 2024 } ) ) Qty All Dates = //total qty for all dates regardless of date range selected CALCULATE ( SUM ( 'DataTable'[QTY] ), ALL ( 'DatesTable' ) )Create another measure that responds to the date slicer selection:
Qty Selected Dates = SUM ( 'DataTable'[QTY] )Use the date column from the dates table and the fruit column from your fact table and the measures
Please see attached sample pbix.
11 Replies
Thanks for the reply from danextian , please allow me to provide another insight:
Hi Ania26 ,Here are the steps you can follow：
1. Create measure.
All Dates = COUNTX( FILTER( ALLSELECTED('Table'),[Fruit]=MAX('Table'[Fruit])),[Date])Dates from 2023 till 2024 = SUMX( FILTER(ALLSELECTED('Table'),[Fruit]=MAX('Table'[Fruit])),[QTY])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hello, thank you for your post. When I use dates filter in your pbix then numbers are changing for both columns when TTL QTY should be constant despite of the selected date range.
Try this meaure
