calculating qty for previous months wrt to selected date in slicer

[Anonymous]

Hi,

if

i select 9/10/2019 in slicer as above

the below is measure for getting qty between starting of that month(9/1/2019 to 9/10/2019) 

Measure = CALCULATE([Quantity],DATESBETWEEN('Calendar'[Date],STARTOFMONTH('Calendar'[Date]),LASTDATE('Calendar'[Date])))

in the same way i also has to get last months qty i.e (8/1/2019 to 8/10/2019), (7/1/2019 to 7/10/2019), (6/1/2019 to 6/10/2019) as shown in below

Thank you.

[Ashish_Mathur]

Hi,

To your visual, drag month from the Calendar Table.  Also, ensure that the Date slicer is built from the Calendar Table.  Try this measure

=CALCULATE([Quantity],DATESBETWEEN(Calendar[Date],EOMONTH(MIN(Calendar[Date]),-2)+1,EDATE(MIN(Calendar[Date]),-1)))

Hope this helps.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Mariusz's suggestion should be useful.

If you need a date slicer to be selected, then you could create a date table which has no relationship with your main data table.

then use the "date" column from this table to a slicer.

date table = CALENDARAUTO()

Then create measures

selected date = MAX('date table'[Date])

Measure = CALCULATE(SUM(Sheet7[value]),FILTER(Sheet7,DAY(Sheet7[date])<=DAY([selected date])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

Hi @Anonymous 

You can add a Day column into your calendar table and use it as slicer like below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.