Forum Discussion

Nihed's avatar
Nihed
Icon for Helper III rankHelper III
4 years ago

Dax need

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

4 Replies

    • Nihed's avatar
      Nihed
      Icon for Helper III rankHelper III
      4 years ago

      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:

       

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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