Showing data weekly or monthly dynamically

[Anonymous]

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:

SalesDate	YearMonth	IsoYearWeek	DimValue	Dim
01.01.2023	2023M01		2023M01	Monthly
02.01.2023	2023M01		2023M01	Monthly
03.01.2023	2023M01		2023M01	Monthly
...	...		...	Monthly
01.01.2023		2022W52	2022W52	Weekly
02.01.2023		2023W01	2023W01	Weekly
03.01.2023		2023W01	2023W01	Weekly
...		...	...	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?

[Anonymous]

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]

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

[Anonymous]

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.

[OwenAuger · Accepted solution]

Hi there @Anonymous 

Now that field parameters are available, I would recommend using that feature as a means of changing the field used on the axis.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Using field parameters avoids the modelling used in the post you linked to (wow, that was 7 years ago!).

Please post back if more guidance needed.

Regards