Dax values returned in visual does not match with data

[sivarajan21]

Hi Team,

We have usage column in our TargetTimeseries table as below. Now we have to create dax for Target consumption per day.

We need to divide this usage column with above Number of days between Startdate & Enddate to get the daily consumption. so we created a dax measure as below:

Target consumption per day = 
VAR minDate = MIN( 'Calendar'[Date] )
VAR maxDate = MAX( 'Calendar'[Date] )
RETURN
    SUMX(
        TargetTimeSeries,
        DIVIDE(TargetTimeSeries[Usage],IF(
            TargetTimeSeries[EndDate] < minDate || TargetTimeSeries[StartDate] > maxDate,
            BLANK( ),
            INT( MIN( TargetTimeSeries[EndDate], maxDate ) - MAX( TargetTimeSeries[StartDate], minDate ) ) + 1
        )
    )
    )

Below is the output in visual:

we did a data analysis with data in model to find whether values returned by measure in visual is accurate.

So we took NetworkRail-3429. For below filter(1st April 2023) on StartDate,EndDate will show 31s March 2024 for this start date,  usage column should add up to give  12519260.(Took it to excel) 

Then according to above 'Target consumption per day' dax, dividing this 12519260 with Number of days between startdate & EndDate which is 365. The result should be 12519260/365 = 34299.

This 34299 should be populated for all months of table visual as below:

But we are not sure where & how below values are calculated in below table visual:

We suspect because there is no relationship between Enddate(TargetTimeSeries) and Date(Calendar) & only startdate-Date relation is active this issue occurs. But not sure how to resolve this issue.

 
The current relationship between these two tables is as below:

Could please advise me on this?

Please let me know if you need further info!

PFA file here TimeSeries Test.pbix

Also, FYR PFA file of filtered Network Rail data NetworkRail3429 data.xlsx

Many thanks in advance!

@marcorusso @Greg_Deckler @amitchandak @jgeddes @Anonymous @Anonymous @Ahmedx @v-cyu 

[marcorusso]

I'd say the report is probably correct. When you filter the data view, the filter is not applied to the report.

If you want to create a filter on the start date at the report level and see the effects of that filter in the cost applied to the following months, you should duplicate the date column and apply different filters. However, I guess you only wanted to test your calculation, so it would be easier to compute the target consumption per day in each column, either in Power Query or DAX. A calculated column in DAX can be:

DailyConsumption = 
VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate]
VAR Result = TargetTimeSeries[Usage] / _Days
RETURN Result

Then, you can apply the events in progress pattern and write the following calculation:

Target consumption per day = 
VAR minDate = MIN ( 'Calendar'[Date] )
VAR maxDate = MAX ( 'Calendar'[Date] )
RETURN CALCULATE (
    SUM ( TargetTimeSeries[DailyConsumption] ),
    REMOVEFILTERS ( 'Calendar' ),
    KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ),
    KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate )
)

You can also avoid the calculated column by using a single measure:

Target consumption per day = 
VAR minDate = MIN ( 'Calendar'[Date] )
VAR maxDate = MAX ( 'Calendar'[Date] )
RETURN  CALCULATE (
    SUMX ( 
        TargetTimeSeries,
        VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate]
        VAR Result = TargetTimeSeries[Usage] / _Days
        RETURN Result
    ),
    REMOVEFILTERS ( 'Calendar' ),
    KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ),
    KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate )
)

Pay attention to the data because when you consider "April 2023" you have rows with a start date other than April 1, 2023.
I hope it helps.

[sivarajan21]

Hi  @marcorusso, 

Thanks for your patience Sir!

We have tested this and it works exceptionally well! Also, I was able to understand events in progress clearly well this time.

In another case, I am confused on getting Target consumption per month from the same measure. What I mean is, from below measure we are able to calculate 'target consumption per day' which is great!.

with this daily value, we now need to use this daily value to multiply by the number of days in the month of the visual to apportion this consumption correctly.

Dax

Target consumption per day = 
VAR minDate = MIN ( 'Calendar'[Date] )
VAR maxDate = MAX ( 'Calendar'[Date] )
RETURN  CALCULATE (
    SUMX ( 
        TargetTimeSeries,
        VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate]
        VAR Result = TargetTimeSeries[Usage] / _Days
        RETURN Result
    ),
    REMOVEFILTERS ( 'Calendar' ),
    KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ),
    KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate )
)

for example,

ie. if the target table started on 17/04/2023 and ended on 31/03/2024 then the daily value would be multiplied by 14. because there are 14 days in April that this relates to. green color indicates what we already achieved.

Yellow color(Target consumption per month) is what we need to find. FYR, PFA below excel file that contains few formulas for better understanding NetworkRail3429 data.xlsx

Target consumption per month should sum up this 14 days values and display for its respective month. 

PFA file TimeSeries Test.pbix

Please advise!

Thanks in advance!

@marcorusso @Greg_Deckler @Ahmedx @amitchandak @Anonymous @Anonymous 

[marcorusso · Accepted solution]

Something like that?

Target consumption = 
VAR minDate = MIN ( 'Calendar'[Date] )
VAR maxDate = MAX ( 'Calendar'[Date] )
RETURN  CALCULATE (
    SUMX ( 
        TargetTimeSeries,
        VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate] +1
        VAR _DailyConsumption = TargetTimeSeries[Usage] / _Days
        VAR _StartDate = MAX ( TargetTimeSeries[StartDate], minDate )
        VAR _EndDate = MIN ( TargetTimeSeries[EndDate], maxDate )
        VAR _DaysDisplayed = _EndDate - _StartDate + 1
        VAR Result = _DailyConsumption * _DaysDisplayed
        RETURN Result
    ),
    REMOVEFILTERS ( 'Calendar' ),
    KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ),
    KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate )
)

[sivarajan21]

Hi @marcorusso ,

Your solution is a gem!

This is bringing a correct values and we have tested it for multiple use cases! It works!

I have become a big fan of keep filters Keepfilters  and remove filters.

Is there any sqlbi article on remove filters?

I will close this query & accept this as solution!

Many thanks!

[marcorusso]

Look at related articles: REMOVEFILTERS – DAX Guide

[sivarajan21]

Hi @marcorusso ,

Apologise for the delay in response!

Thanks for your quick & elegant response along with amazing dax!😍

Still we are testing it for multiple scenarios! I will work on it and get back with results!

Thanks for your patience!

[Greg_Deckler]

@sivarajan21 Maybe try this:

Target consumption per day = 
VAR minDate = MIN( 'Calendar'[Date] )
VAR maxDate = MAX( 'Calendar'[Date] )
RETURN
    SUMX(
        ALL( TargetTimeSeries ),
        DIVIDE(TargetTimeSeries[Usage],IF(
            TargetTimeSeries[EndDate] < minDate || TargetTimeSeries[StartDate] > maxDate,
            BLANK( ),
            INT( MIN( TargetTimeSeries[EndDate], maxDate ) - MAX( TargetTimeSeries[StartDate], minDate ) ) + 1
        )
    )
    )

[sivarajan21]

Hi @Greg_Deckler ,

Thanks for your quick response on this!

Tried your measure, but this doesn't give the desired result:

My dax works were StartDate & EndDate have same months(may month in this below example):

But dax finds it difficult when there are multiple months(April 2023 to March 2024) between date range as below:

Please advise how to deal this!

Thanks in advance!

@Greg_Deckler @marcorusso @Ahmedx @amitchandak @Anonymous @Anonymous