QTY based on the date range

[Ania26]

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?

[Ajithkumar_P_05]

Try this meaure

[Anonymous]

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

[Ania26]

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. 

[danextian]

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.

[Ania26]

One column with TTL QTY, all dates, second column with QTY beased on the date range

[danextian]

Your sample data please.

[Ania26]

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

[danextian · Accepted solution]

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.

[Ania26]

Hello, thank you. It does work with Calendar Table. Is there a way to do it without?

[danextian]

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.

[Ania26]

Thank you.