Periods parameter for user flexibility

[Sammy22]

Hello,

I have a report which currently has set periods, P1 is 2023 and P2 is 2024. Users want to be able to change the date ranges of these periods, for example, they may want to look at the first half of 2022 vs 2023. Below is an example of how i would want this to work.

I have tried to follow the below process. I can create 2 calendar tables, 2 date paramters however, i cannot 'bind to parameter'. This isn't visible. Appreciate the help.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

[Irwan · Accepted solution]

hello @Sammy22 

the best way is using filter pane as mentioned by @lbendlin .

if you want tricky way, here is an example of what i did for my project.

1. create a duplicate table of your original table, something like this.

Table 2 = 'Table'

2. create P1 and P2 measure which P1 takes value from original table and P2 takes value from duplicated table.

P1 = 
IF(
    ISFILTERED('Table'),
    SUM('Table'[Orders])
)

P2 = 
IF(
    ISFILTERED('Table 2'),
    SUM('Table 2'[Orders])
)

3. plot your slicer and measure. similarly as above, slicer 1 from original table and slicer 2 from duplicated table.

Hope this will help.

Thank you.

[Sammy22]

thanks for your help! What if i also want to group by a field that exists in both tables. If i select a field from Table 1 then the orders will be broken down for Table 1 (P1) only and Table 2 (p2) Orders will just show a total.

[Irwan]

hello @Sammy22 

i might be misunderstood but if you mean by "group by a field" is calculation result from selecting field, then you can add your field as filter so it will calculate each of those fields.

something like this, calculate(sum('Table'[Orders]),filter(allselected('Table'),'Table[Field]=.....))

otherwise, please share your sample data and your desired outcome.

Thank you.

[Sammy22]

Thanks for your help. It would be like the below where there are can be up to 40 different countries. I would be summing up the orders by country by period.

[Irwan]

hello @Sammy22 

to do this, you need to define relationship since P1 and P2 will be group into same country category.

1. create a new table with following DAX.

Country = SUMMARIZE('Table','Table'[Country])

2. create a relationship between the new table and P1 tbl and P2 tbl

3. add country value from the new table (not country from table P1 and table P2).

4. change P1 and P2 DAX into this DAX if you want to make those blank value into zero. This is basically same DAX with additional if statement for zeroing blank value.

P1 = 
var _Value = 
IF(
    ISFILTERED('Table'),
    SUM('Table'[Orders])
)
Return
IF(
    ISBLANK(_Value),
    0,
    _Value
)

P2 = 
var _Value = 
IF(
    ISFILTERED('Table 2'[Date]),
    SUM('Table 2'[Orders])
)
Return
IF(
    ISBLANK(_Value),
    0,
    _Value
)

Hope this will help.

Thank you.

[lbendlin]

For maximum flexibility use the Filter Pane and teach your users how to operate it.

[Sammy22]

Are you able to share more details? What do you mean by the filter pane?

[lbendlin]

This one