Dynamically setting Date and Quarters in Report

[vishy86]

Hi,

I have a report where -

Database Load Frequency = Every month

Report to be refreshed = Every quarter month (Calendar Quarters - Mar, Jun, Sep and Dec)

For testing purposes, I have set a Custom Filter within Power BI for Booked Date which is as shown. Booked Date currently has all the month dates from Jan 2018 until Dec 2019. There is another calculated column Booked Quarter which is nothing but a Year and Quarter extract from the Booked Date column.

I want only the relevant quarters shown in the report. Instead of "This Year" and "Last Year", I would like to know if there is any way to pass a Measure or Parameter which says YEAR(MAX(Booked Date)) and YEAR(MAX(Booked Date))-1, which would ensure that even if the user mistakenly runs the report before the first quarter of the year, This Year will still show the YEAR(MAX(Booked Date)) which in this case is 2019 and not the calendar year which is 2020, so as to ensure the relevant quarters are shown.

I tried using parameters but could not find a way to create a dynamic parameter via some formule or so. I also tried changing Booked Quarter to a Date so as to use Relative date filtering option.

Thanks,

Vishy

[amitchandak]

In case you can have Dated in the table. You can create a calendar that starts from the Min date of your table to the max date of your table.

New table

Date = calendar (Min(Table[Date]),Max(Table[Date]))

Now all you ytd or lytd calculation using calendar and time intelligence will work on the last date of the calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/