Forum Discussion
QTY based on the date range
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.
Hi Ania26 , You mean if you hava row with dates from Jan 1 to Jabn 31, a column that will show the total for the whole month regardless of the date and another column showing the total for each date? Please provide a workable sample data and your expected result from that.
One column with TTL QTY, all dates, second column with QTY beased on the date range
Your sample data please.
Fruit Date QTY A 3/3/2023 1 B 3/3/2024 2 A 2/2/2021 3 b 2/2/2021 4 Fruit All Dates Dates from 2023 till 2024 A 4 1 B 6 2 Last column would change depending on the date selection
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.