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.
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.
Hello, thank you. It does work with Calendar Table. Is there a way to do it without?
You can use
Qty All Dates = CALCULATE ( SUM ( 'DataTable'[QTY] ), ALL ( 'DataTable'[Date] ) )Please note that the filter modifier is applied to 'DataTable'[Date] so if there are filters coming from other date-related dimensions in your fact table, that will not show the value for all dates anymore.