How to create dynamic Reporting, Using Slicer?.

[leviIM]

Data

Date		Branch		Sales
31-08-2022		1		1000
31-07-2022		1		900
31-06-2022		1		1000
31-12-2021		1		800

Date Picker

31 - 08 - 2022

Report Sample

Branch		Last Year 31-12-2021		Previous Date 31-07-2022		Current Date 31-08-2022		Growth Montly		Growth Yearly
1		800		900		1000		100		200

Description.
How to create a dynamic Report on Power BI, so when i pick a date, it will generate value like report sample above. that will show current date, previous date, and last year date. with measure the growth monthly and yearly. Thanks.

[Anonymous · Accepted solution]

Hi  @leviIM ,

Here are the steps you can follow：

1. Create measure.

Last Year =
var _select=
SELECTEDVALUE('Date Picker'[Date])
return
SUMX(FILTER(ALL(Data),'Data'[Date]=
EOMONTH(
DATE(
    YEAR(_select)-1,12,1),0)),
    [Sales])

Previous Date =
var _select=
SELECTEDVALUE('Date Picker'[Date])
return
SUMX(FILTER(ALL(Data),'Data'[Date]=
DATE(
    YEAR(_select),MONTH(_select)-1,DAY(_select))),
    [Sales])

Current Date =
var _select=
SELECTEDVALUE('Date Picker'[Date])
return
SUMX(FILTER(ALL(Data),'Data'[Date]=
_select),
    [Sales])

Growth Montly =
[Current Date] - [Previous Date]

Growth Yearly =
[Current Date] - [Last Year]

2. Result:

If you need pbix, please click here.

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

[leviIM]

Thank you for solution, i can modify this for my case.

[leviIM]

@amitchandak 
i am using your formula, but the value still same with the current date picker.

Current CIF = SUM('Sheet1 (2)'[cif])

Previous CIF = CALCULATE(SUM('Sheet1 (2)'[cif]),DATEADD('Sheet1 (2)'[date].[Date],-1,MONTH))

[amitchandak]

@leviIM , if date picker is connected and coming from date table

Month behind Sales = CALCULATE(SUM(Sales[Sales]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales]),dateadd('Date'[Date],-1,Year))

Last Day = CALCULATE(SUM(Sales[Sales]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(SUM(Sales[Sales]), previousday('Date'[Date]))

Same date = SUM(Sales[Sales])

for MOM

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

For YOY

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))

if date is coming from an independent table. Replace today with maxx(allselected('Date') , 'Date'[Date]) in this blog

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

it has all measures you need

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
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
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...