Dynamic selection of datesand calculations

[Anonymous]

Hi All, 

Need help on thebelow scenario.

If the data has a dates  (Data attached) and the financial year starts from Apr. Has two filters in the report Financial Year and Date and total sales. need help to calcualte sum of sales based on month selection. 

Example: If selected Jun'21 in month filter, should able to calcualte sales for the months of Apr'21 to Jun'21

Exp2: If selected Mar'21 in month filter, should able to calcualte sales for the months of Jan'21 to Mar'21

based on the month selection it should dynamically pick the durationand calcualte the sales figures.

https://drive.google.com/file/d/1bT9g09dzbd70cGDQXXOZnk5r1MJ-9EWQ/view?usp=sharing 

Please let me know if you need further information

Regards

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

You can easily create a measure to diaplay the result.

Like this:

Measure = 
VAR a =
    DATEADD ( 'Table'[Date], -1, QUARTER )
RETURN
    CALCULATE ( SUM ( Sheet1[sales] ), FILTER ( ALL ( 'Table' ), [Date] IN a ) 

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , this seems like you need QTR till date?  that you can do with a date table

example

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

YTD based on 3/31 year end date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0