Delta not showing correct value when filtered

[KL008]

Hi, I have a problem.
So, I am trying to create a matrix in powerbi showing the total number of controls per taxonomy. In the rows we have the taxonomy, in the values we have #controls and Delta controls. The delta shows the change in number of controls from prior month. I also want a slicer to filter on month, and other characteristics of the controls.

The data all exists in a historical table called FactControlRelations and the circumstance is as follows:
1. each control can be listed out multiple times for the following reasons:

a. each control can be linked to one or more risks via a column
b. each combination of a control and risk can be related to multiple organisation levels
c. data is appended each month updating the information regarding the controls and their relations, and a Date column shows the month this data was refreshed

In the matrix the goal is to display the number of controls per taxonomy (Each risk has one relation to a taxonomy). For this I have created the following:

calculated column 

ControlRiskCombination =

FactControlRelations[Guid] & "+" & FactControlRelations[LinkedRisks]

measure

PreviousMonthControlRiskCombinationCount =

VAR PreviousMonthStart = STARTOFMONTH(DATEADD(FactControlRelations[Date], -1, MONTH))

VAR PreviousMonthEnd = ENDOFMONTH(DATEADD(FactControlRelations[Date], -1, MONTH))

RETURN

CALCULATE(

    DISTINCTCOUNT(FactControlRelations[ControlRiskCombination]),

    FILTER(

        FactControlRelations,

        FactControlRelations[Date] >= PreviousMonthStart &&

        FactControlRelations[Date] <= PreviousMonthEnd

    )

)

measure

DeltaControlRiskCombinationCount =

[CurrentMonthControlRiskCombinationCount] - [PreviousMonthControlRiskCombinationCount]

When I add PreviousMonthControlRiskCombinationCount and DeltaControlRiskCombinationCount into cards with no filters applied on the page they show the correct values, and even when I filter on taxonomy it is correct. However, once I add a date filter the card with PreviousMonthControlRiskCombinationCount shows (Blank) and the delta just shows the same as the count.

Can somebody help me to find a solution please?

[v-achippa]

Hi @KL008,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Anonymous for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @KL008,

We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @KL008,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Anonymous · Accepted solution]

Hi @KL008 ,

After adding a date slicer, for example selecting June 2023, the filter context has limited FactControlRelations[Date] to June 2023. Therefore, when DATEADD calculates the previous month (May 2023), there are no rows visible in the current context for May, which is why your count for the previous month returns null.

Since your delta measure is defined as:

If [PreviousMonthControlRiskCombinationCount] is calculated as blank due to the filter context, subtracting 0 means your delta will be the same as the current month count. That's why you'll see the same value.

My suggestion would be to create a separate date table, or if you have a date table linked to a fact data table, you could utilize a time-smart function, for example:

PreviousMonthControlRiskCombinationCount =
CALCULATE(
    DISTINCTCOUNT(FactControlRelations[ControlRiskCombination]),
    PREVIOUSMONTH('Calendar'[Date])
)

The PREVIOUSMONTH function automatically calculates the time period based on the full date in the date table.

If you don't want to create a date table, there is a workaround: you can do this by using the ALL or REMOVEFILTERS function on the Date column:

PreviousMonthControlRiskCombinationCount =
VAR PreviousMonthStart =
    STARTOFMONTH(DATEADD(ALL(FactControlRelations[Date]), -1, MONTH))
VAR PreviousMonthEnd =
    ENDOFMONTH(DATEADD(ALL(FactControlRelations[Date]), -1, MONTH))
RETURN
CALCULATE(
    DISTINCTCOUNT(FactControlRelations[ControlRiskCombination]),
    ALL(FactControlRelations[Date]),
    FactControlRelations[Date] >= PreviousMonthStart,
    FactControlRelations[Date] <= PreviousMonthEnd
)

If the problem is not resolved, please provide a Power BI Desktop file in progress (with sensitive information removed) that fully covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set up public access), SharePoint, or a Github repository, and then share the URL of the file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!