Default Date With Calculation Group

[Anonymous]

Hello,

I am trying to create a default date in the Power BI report with the help of this video:

https://www.youtube.com/watch?v=KBEsPGxDxHM

I want the report to be showing by default the most recent (max) Report Date (fact Orderbook table).

This is the calculation I have created based on the above video:

VAR _IsDateFiltered =
    CALCULATE ( ISFILTERED ( 'dim Calendar'[Date] ), ALLSELECTED () )
VAR _LastReportDate =
    CALCULATE ( MAX ( 'fact Orderbook'[Report Date] ), REMOVEFILTERS () ) + 1
VAR _Days = 7
VAR _Result =
    IF (
        _IsDateFiltered,
        SELECTEDMEASURE (),
        CALCULATE (
            SELECTEDMEASURE (),
            KEEPFILTERS ( DATESINPERIOD ( 'dim Calendar'[Date], _LastReportDate, -1, DAY ) )
        )
    )
RETURN
    _Result

However, when I select the "Recent" from the slicer, nothing is changing... What am I doing wrong?

[Anonymous]

@v-jingzhang 

One more thing here - sorry. I have noticed that it works perfectly fine when we have a slicer that allows multiple selections.

How can I make it work with a single-select slicer?

[v-jingzhang]

@Anonymous You can switch the slicer to single select mode in Format pane. Under Selection controls > Single select.

[Anonymous]

My point was that if I change this to single select then the calculation group doesn't work anymore (because by default single select "forces" you to select any date)... do you have an idea how can we make this calculation group with a default date work with a single select?

[v-jingzhang]

Do you mean your default date range is a single date (e.g. the last date in fact table) and you want to have another date slicer for user to pick a single date? The whole page should reflect data on a single date (either the selected one or the default one)?

[Anonymous]

Yes, exactly! Would it be possible?

[v-jingzhang]

To be honest, it's the first time I use calculation group so I'm not sure at present. I need some time to test it. Thanks for your understanding. I will update here if have any idea. 

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I followed the video and created a sample for your reference. You can download it at bottom.

I guess if you drag columns directly into visuals as values fields? If so, when a model has calculation groups, the implicit column aggregations will fail. Implicit column aggregations in Power BI are not supported for models with calculation groups. This is by design. See Tabular Editor - Calculation groups - Microsoft Power BI Community

As a result, when you include a calculation group in this model, you need to create measures to aggregate columns. Then put them into visuals as values fields.

E.g. in my sample, Close Total is a simple measure SUM('FactTable'[Close]). In below image, you will find that the table having Close Total can be filtered by the Default Dates slicer, but the table having Close column cannot be filtered. If you right click on the Close column in Values card of the table visual, you will find that there are no aggregate types to select in its right-click menu. 

Let me know if you have any questions.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Chiblaq]

Hello,

I have exactly the same problem just like the first person described it. I have implemented the solution you recommended by creating a simple measure and putting it into visuals as values fields but it is not still working. Do you recommend I do any other thing to resolve it?

[Anonymous]

Yes, that's correct. I was using default column reference/aggregations instead of measures. Now it works! Thank you so much. 🙂