Show lines in a line chart for intervals where the values of both measures as known

[danisharoon]

Dear concerned

I have a line chart displaying two measures each belonging from a different table. There is a lead slicer that applies to one measure. Applying a lead of 2 means that there are 2 time intervals where one measure has null values, and the other measure has values. I would want to display the values in intervals only where the values of both measures as known. I am using the two measures to calculate MAPE. Ideally I would want to calculate MAPE for only intervals having values in both of the measures.

My canvas looks as follows:

You can see that for April, we have the data for actual but not for prediction. Hence ideally I would want the Actual data to only appear till March (i.e. months for which prediction data is also available)

If I apply the lead of 3 on prediction, then it looks as follows:

In this case, data for Actual should appear uptill Jan'22.

Moreover with the current setting, MAPE in the scorecard is calculated incorrectly. Considering the first case, while calculating MAPE, it is taking the sum of actual for 14 intervals whereas taking the sum of 13 intervals for predictions. Whereas ideally it should only sum for intervals in which the value of both measures is known.

[sturlaws · Accepted solution]

Hi,

as you have not provided any sample data, dax code or sample file, I can only show you a generic suggestion on how to solve your issue.

You can write the [Actual]-measure something like this, assuming you have a date table:

MeasureSales =
VAR _dates =
    CALCULATETABLE (
        VALUES ( Dates[Month] ),
        FILTER ( ALL ( Dates ), NOT ISEMPTY ( 'Predictions' ) )
    )
VAR _currentMonth =
    CALCULATE ( SELECTEDVALUE ( Dates[Month] ) )
RETURN
    IF ( _currentMonth IN _dates, SUM ( 'Actuals'[Actual] ), BLANK () )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly

[danisharoon]

Hello there!

Thank you for the help. This did solve the problem in terms of the line chart, however the problem still remains while calculating the MAPE measure.

So, as recommended, I created a new measure as follows:

However I am unable to use this newly created measure while creating another measure.

Danish

[sturlaws]

If you want assistance with the measures, please provide the code for them, or share a sample pbix-file

How to Get Your Question Answered Quickly

[danisharoon]

Hi @sturlaws 

Link to the pbix file is as follows:

https://nflpk-my.sharepoint.com/:u:/g/personal/danish_haroon_nfoods_com/EZ04U6OMVt5Dru_4o-quMJ0BW1Kr...

The issue is coming in the Total Actuall measure of the fact_actual table

[v-chenwuz-msft · Accepted solution]

Hi @danisharoon ,

Please try this expression to calculate the actual.

Actuall = 
VAR _sum =
    SUMMARIZE (
        'fact_actual',
        [MY name],
        "act",
            CALCULATE (
                SUM ( fact_actual[Actual] ),
                FILTER ( dim_my, NOT ISEMPTY ( fact_pred ) )
            )
    )
RETURN
    SUMX ( _sum, [act] )

And the MAPE:

Measure MAPE = DIVIDE (ABS(fact_actual[Actuall] - fact_pred[Total Pred]), fact_actual[Actuall], 0) 

Result:

Modified Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sturlaws · Accepted solution]

Hi,

as you have not provided any sample data, dax code or sample file, I can only show you a generic suggestion on how to solve your issue.

You can write the [Actual]-measure something like this, assuming you have a date table:

MeasureSales =
VAR _dates =
    CALCULATETABLE (
        VALUES ( Dates[Month] ),
        FILTER ( ALL ( Dates ), NOT ISEMPTY ( 'Predictions' ) )
    )
VAR _currentMonth =
    CALCULATE ( SELECTEDVALUE ( Dates[Month] ) )
RETURN
    IF ( _currentMonth IN _dates, SUM ( 'Actuals'[Actual] ), BLANK () )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly