Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Showing data weekly or monthly dynamically

I have sales data with SaleDate column. Now I'd like to have one drop down selector (filter) with options "Monthly" and "Weekly" where one will be able to select wether this sales data will be represented on monthly or weekly basis. (I do not want two charts exchanged with bookmarks!) So the X axis should be changed dynamically.

 

I was researching the forum and found how dynamically choose different dimensions for X axis using kind a bridging table (Dynamic change in X Axis by OwenAuger) but for this solution I need the data table like this:

 

SalesDateYearMonthIsoYearWeekDimValueDim
01.01.20232023M01 2023M01Monthly
02.01.20232023M01 2023M01Monthly
03.01.20232023M01 2023M01Monthly
...... ...Monthly
01.01.2023 2022W522022W52Weekly
02.01.2023 2023W012023W01Weekly
03.01.2023 2023W012023W01Weekly
... ......Weekly

 

Ok, "YearMonth" and "IsoYearWeek" columns are redundant here but...

How to automatically create such a table with DAX?

Also, maybe there is an easier solution?

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Yea, OP was posted several years ago but, you see, you're still here to help. That's wow, as well. 🙂

    Thanks for your prompt response. It seems that field parametera are exactly what I need.

    Seems that Microsoft concluded too that people will need this feature.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    It seems that "Field Parameters" feature is very new in PowerBI and unfortunately my Power BI Desktop RS (january-2023) does not support yet! Hmm

    • OwenAuger's avatar
      OwenAuger
      Super User
      2 years ago

      Ah, I see!

      It turns out that the Field Parameters feature is still in preview so it won't be in Report Server versions yet.

       

      I have attached a PBIX with an example using a modelling approach instead.

       

      1. Create a Date Grouping table that looks something like this.

      It contains every date from the 'Date' table grouped into both Weeks and Months.

      Date Grouping is either "Weekly" or "Monthly".

      Label is the label that will appear in the visual, and has to be of type text to handle different grouping types.

      Label Sort is constructed to give the appropriate sort order for each of Weekly/Monthly labels, but must have a 1:1 mapping with Label.

      Date contains the dates corresponding to each Label.

      This can be generated with Power Query or DAX. I used DAX in my example:

      Date Grouping = 
VAR Monthly =
    SELECTCOLUMNS (
        SUMMARIZE ( 'Date', 'Date'[Start of Month], 'Date'[Date] ),
        "Date Grouping", "Monthly",
        "Label", FORMAT ( 'Date'[Start of Month], "mmm-yyyy" ),
        "Label Sort Original", 'Date'[Start of Month],
        "Date", 'Date'[Date]
    )
VAR Weekly =
    SELECTCOLUMNS (
        SUMMARIZE (
            'Date',
            'Date'[Fiscal Year Week],
            'Date'[Fiscal Year Week Number],
            'Date'[Date]
        ),
        "Date Grouping", "Weekly",
        "Label", 'Date'[Fiscal Year Week],
        "Label Sort Original", 'Date'[Fiscal Year Week Number],
        "Date", 'Date'[Date]
    )
VAR Combined =
    UNION ( Monthly, Weekly )
VAR Result =
    SELECTCOLUMNS (
        Combined,
        "Date Grouping", [Date Grouping],
        "Label", [Label],
        "Label Sort",
            RANK (
                DENSE,
                Combined,
                ORDERBY ( [Date Grouping], ASC, [Label Sort Original], ASC )
            ),
        "Date", [Date]
    )
RETURN
    Result

       

      2. Create a 1:many bidirectional relationship between 'Date'[Date] and 'Date Grouping'[Date].

      3. Create a single-selection slicer using 'Date Grouping'[Date Grouping].

      3. Place 'Date Grouping'[Label] on the axis of the visual.

      Hopefully you can adapt this to your particular model 🙂

       

      Regards