Running total not running correctly when using Userelationship and having inactive relationship

[AU555]

Hi all,

I have a problem with some of my calculations and I cannot figure out, what am I doing wrong.

I want to calulate cummulative total for for values and display it in line chart:

In my Table X I have all neccesarry data and I have Actual date and Forecast date. In this table I have also my calculated column which will be used for running total: measure - "unique values" (unique values = DISTINCTCOUNTNOBLANKS(findmaximumvaluesforproduct)  .

Since I have two different dates, I have created a calendar table, and I created relationships between my X table and calendar table.

1. Firstly, I have created relationship Date (calendar) - actual date (x table) with active relationship

And then date (calendar) - forecast dates (x table) with inactive relationship

I have then used function USERELATIONSHIP to activate relationship between my forecast dates and calendar table:

Forecast relationship =

CALCULATE([unique values];

USERELATIONSHIP('Date'[Date]; 'x (Table)'[forecastdates]))

And then I have created a running total for both forecast and actual dates:

Forecast running total =

CALCULATE(

    [forecast relationship];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

    )

)

Actual running total =

CALCULATE(

    [unique values];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

    )

)

However, I have problem with this calculation when the forecast values end after the values for actual dates have been completed: In my example, forecast and actual values will always end up at the same value. So if the running total for actual date for one product will be 100, the forecast will be also always 100, just the dates will be different, depending how we have kept up with our schedule. So lets say actual value for product A will end on 20.10.2018 and it will display 60, that is when my line chart stops, even tough my forecast value has been only 56 on the 20.10.2018 and will be 60 on the 30.10.2018.

The data are still there and when I plot only my forecast data into the plot, I can see that the graph ends at 30.10.2018, therefore the actual values should go straight for 10 days and forecast should keep increasing until 30.10.2020.

However when I use this method it is ignoring those dates and the x axis stops when the actual dates (therefore the one with active relationship) reach their goal. Ignoring the dates with inactive relationship. When it is the opposite case, so when forecast values end before actual values, the forecast line goes straight until it meats it goals and theefore also meets actual values. (forecast is dashed and actual is solid). I want this view also when forecast ends after actual dates:

I have tried to google this, and I have read that I should try to make both relationships inactive and therefore have two measures with USERELATIONSHIP function.

So I have put both the relationships inactive and created the following calculations:

actual relationship =

CALCULATE([unique values];

USERELATIONSHIP('Date'[Date]; 'x(Table)'[actualdates]))

Actual =

CALCULATE(

    [actual relationship];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

    )

)

The calculation for forecasts I kept the same as is above.

This change solved the problem and displayed also the forecasting values even tough they have finished after the actual values have finished, but it has created this view, where the values wont stop until the end of my calendar table: (which looks wierd if it is running straight for 10 years…)

I have tried several improvement is my running total calculations, but they simply dont work…

1. I have tried this calculation:

actual1 =

VAR LastDayAvailable =

CALCULATE (

MAX ( 'x (Table)'[actualdates] );

ALL ( 'x(Table)')

)

VAR FirstDayInSelection =

MIN ( 'Date'[Date] )

VAR ShowData =

(FirstDayInSelection <= LastDayAvailable)

VAR Result =

IF (

ShowData;

CALCULATE(

[actual relationship];

FILTER(

ALLSELECTED('Date'[Date]);

ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

)

)

)

RETURN Result

forecast1 =

VAR LastDayAvailable =

CALCULATE (

MAX ( 'x(Table)'[forecastdates] );

ALL ( 'x(Table)')

)

VAR FirstDayInSelection =

MIN ( 'Date'[Date] )

VAR ShowData =

(FirstDayInSelection <= LastDayAvailable)

VAR Result =

IF (

ShowData;

CALCULATE(

[forecast relationship];

FILTER(

ALLSELECTED('Date'[Date]);

ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

)

)

)

RETURN Result

1. Also adding the condition at the end of my running total calculation

Actual =

CALCULATE(

    [actual relationship];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)&&'Date'[Date]<= MAX('Date'[Date])

    )

)

However none of these have worked.

Therefore, I want to ask if some of you have some idea how to solve this? I am not sure if it is not problematic, that I am using calendar table, with one (or both) inactive relationships, USERELATIONSHIP function as well as running total. I have used the quick measure from Power BI, as well as tried my own calculations but none of them displayed reult I wanted, any tip?

Hope it is understandable and thank you for you help 🙂 

it is much appriciated!!!! 🙂 

Anastasia

[AU555]

I have a problem with some of my calculations and I cannot figure out, what am I doing wrong.

I want to calulate cummulative total for for values and display it in line chart:

In my Table X I have all neccesarry data and I have Actual date and Forecast date. In this table I have also my calculated column which will be used for running total: measure - "unique values" (unique values = DISTINCTCOUNTNOBLANKS(findmaximumvaluesforproduct)  .

Since I have two different dates, I have created a calendar table, and I created relationships between my X table and calendar table.

1. Firstly, I have created relationship Date (calendar) - actual date (x table) with active relationship

And then date (calendar) - forecast dates (x table) with inactive relationship

I have then used function USERELATIONSHIP to activate relationship between my forecast dates and calendar table:

Forecast relationship =

CALCULATE([unique values];

USERELATIONSHIP('Date'[Date]; 'x (Table)'[forecastdates]))

And then I have created a running total for both forecast and actual dates:

Forecast running total =

CALCULATE(

    [forecast relationship];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

    )

)

Actual running total =

CALCULATE(

    [unique values];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

    )

)

However, I have problem with this calculation when the forecast values end after the values for actual dates have been completed: In my example, forecast and actual values will always end up at the same value. So if the running total for actual date for one product will be 100, the forecast will be also always 100, just the dates will be different, depending how we have kept up with our schedule. So lets say actual value for product A will end on 20.10.2018 and it will display 60, that is when my line chart stops, even tough my forecast value has been only 56 on the 20.10.2018 and will be 60 on the 30.10.2018.

The data are still there and when I plot only my forecast data into the plot, I can see that the graph ends at 30.10.2018, therefore the actual values should go straight for 10 days and forecast should keep increasing until 30.10.2020.

However when I use this method it is ignoring those dates and the x axis stops when the actual dates (therefore the one with active relationship) reach their goal. Ignoring the dates with inactive relationship. When it is the opposite case, so when forecast values end before actual values, the forecast line goes straight until it meats it goals and theefore also meets actual values. (forecast is dashed and actual is solid). I want this view also when forecast ends after actual dates:

I have tried to google this, and I have read that I should try to make both relationships inactive and therefore have two measures with USERELATIONSHIP function.

So I have put both the relationships inactive and created the following calculations:

actual relationship =

CALCULATE([unique values];

USERELATIONSHIP('Date'[Date]; 'x(Table)'[actualdates]))

Actual =

CALCULATE(

    [actual relationship];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

    )

)

The calculation for forecasts I kept the same as is above.

This change solved the problem and displayed also the forecasting values even tough they have finished after the actual values have finished, but it has created this view, where the values wont stop until the end of my calendar table: (which looks wierd if it is running straight for 10 years…)

I have tried several improvement is my running total calculations, but they simply dont work…

1. I have tried this calculation:

actual1 =

VAR LastDayAvailable =

CALCULATE (

MAX ( 'x (Table)'[actualdates] );

ALL ( 'x(Table)')

)

VAR FirstDayInSelection =

MIN ( 'Date'[Date] )

VAR ShowData =

(FirstDayInSelection <= LastDayAvailable)

VAR Result =

IF (

ShowData;

CALCULATE(

[actual relationship];

FILTER(

ALLSELECTED('Date'[Date]);

ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

)

)

)

RETURN Result

forecast1 =

VAR LastDayAvailable =

CALCULATE (

MAX ( 'x(Table)'[forecastdates] );

ALL ( 'x(Table)')

)

VAR FirstDayInSelection =

MIN ( 'Date'[Date] )

VAR ShowData =

(FirstDayInSelection <= LastDayAvailable)

VAR Result =

IF (

ShowData;

CALCULATE(

[forecast relationship];

FILTER(

ALLSELECTED('Date'[Date]);

ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)

)

)

)

RETURN Result

1. Also adding the condition at the end of my running total calculation

Actual =

CALCULATE(

    [actual relationship];

    FILTER(

        ALLSELECTED('Date'[Date]);

        ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)&&'Date'[Date]<= MAX('Date'[Date])

    )

)

However none of these have worked.

Therefore, I want to ask if some of you have some idea how to solve this? I am not sure if it is not problematic, that I am using calendar table, with one (or both) inactive relationships, USERELATIONSHIP function as well as running total. I have used the quick measure from Power BI, as well as tried my own calculations but none of them displayed reult I wanted, any tip?

Hope it is understandable and thank you for you help 🙂 

it is much appriciated!!!! 🙂 

Anastasia

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5