topic Re: Dax need in DAX Commands and Tips

Dax need

Nihed — Fri, 26 Nov 2021 10:33:54 GMT

You will see that there is a quarter selection on dashboard:

This selection comes from an excel file, however it is not great because users sometimes forget to update this excel file.

Instead of that, can we create a table which is dynamically filled with quarters and replace that excel selection?

New table should include quarters from last 5 years until current quarter.

The dashboard should always select current quarter automatically.

how can I please do this

Re: Dax need

amitchandak — Fri, 26 Nov 2021 11:42:40 GMT

Nihed , You need to create column like this in your date table and save that in This Qtr

Switch( True() ,

format(today(), "YYYY - \QQ") = [Qtr Year] , "This Qtr",

[Qtr Year]

)

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA

Re: Dax need

Nihed — Fri, 26 Nov 2021 11:52:50 GMT

thank you for your answer
just for information I don’t have a date table in my report I use an EXCEL file with this data and I wanted to create this data in a direct dynamic table to have "QUARTER SELECTION " in Power BI how I can do please?

the data of the execel file:

Re: Dax need

v-easonf-msft — Wed, 01 Dec 2021 08:16:35 GMT

Hi, Nihed

You can try following code to manually create a calendar table to replace data in EXCEL file.

Calendar date = CALENDAR(DATE(2017,01,01),TODAY()) //calculated table

calculated columns:

Quarter = FORMAT('Calendar date'[Date],"YYYYQ")

Num quarter = VAR _year = YEAR ( TODAY () ) VAR _quarter = QUARTER ( TODAY () ) VAR _last_quarter = IF ( _quarter = 1, _year - 1 & "4", _year & _quarter - 1 ) RETURN IF ( 'Calendar date'[New quarter] = _last_quarter, "last quarter", 'Calendar date'[New quarter] )

Due to the use of calculated table, you still need to refresh the calendar table to get the latest data.

Best Regards,
Community Support Team _ Eason

Re: Dax need

Nihed — Wed, 01 Dec 2021 22:38:01 GMT

Hello,

It works very well I did exactly the same thing, thank you very much for your help

Thank you.