Dynamic calculation along time period

[dirkkoch]

Hi all,

I would need support with the following issue (see Screenshot attached). I have 2 tables (Inbound & Outbound) which are both related to a Calendar table (one to many relation). As a result I want to have a dynamic calculation for the Total stock (yellow marked column, taking into consideration the bold marked cells) always starting with today's date. Support is highly appreciated!

[v-janeyg-msft · Accepted solution]

Hi, @dirkkoch 

You need to create three columns and a measure in calendar table. Because today is the 19th, the data has changed a bit.

Like this:

Column =
VAR a =
    CALCULATE (
        COUNT ( 'Inbound table'[Stock location] ),
        FILTER ( ALL ( 'Inbound table' ), 'Inbound table'[Inbound Date] <= TODAY () ),
        'Inbound table'[Stock location] = "Location 1"
    )
VAR b =
    SUMX (
        FILTER ( 'Calendar table', [Date ] <= EARLIER ( 'Calendar table'[Date ] ) ),
        [ColumnINBOUND]
    )
VAR c =
    SUMX (
        FILTER ( 'Calendar table', [Date ] <= EARLIER ( 'Calendar table'[Date ] ) ),
        [ColumnOUTBOUND]
    )
RETURN
    a + b - c

Measure = IF(MAX('Calendar table'[Date ])<TODAY(),0,1)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[v-janeyg-msft · Accepted solution]

Hi, @dirkkoch 

You can create a column.

Like this:

Column =
VAR a =
    MAXX (
        FILTER (
            'Calendar table',
            [Relative Week] = EARLIER ( 'Calendar table'[Relative Week] )
        ),
        [Date ]
    )
VAR b =
    MAXX ( FILTER ( 'Calendar table', [Date ] = a ), [Total Preview Day] )
RETURN
    b

Best Regards

Janey Guo

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

[dirkkoch]

Feedback is very welcome if anybody can offer support :-). Also if the problem needs to be specified more precisely.

[v-janeyg-msft]

Hi, @dirkkoch 

Your idea is achievable, but I don’t know how the value of 'stock level location 1' and 'sum inbound' and 'sum outbound are calculated'. Can you explain it more clearly and share some sample fake data in table? So we can help you soon.

Best Regards

Janey Guo

[dirkkoch]

Hi, @v-janeyg-msft 

the values are calculated as follows (see also Screenshot with comments shared in post above):
"stock level location 1": Calculation is starting with today's date showing total of all columns "Stock location" from "Inbound table" with value "Location 1" & date<Today (-> in examplary Screenshot "result table"=3 on 15.03.2021).

For each following day the calculation is the "Total stock" result from yesterday (Stock level location 1 + sum Inbound - sum Outbound -> see examples from result table in Screenshot, e.g. 4 on 16.03.2021 -> 4+0-0=4)

"Sum Inbound": Contains count of dates from "Inbound table" - column "Inbound date" where the date in "Calender table" equals "Inbound table" & is >= Today (e.g. Inbound date 13.04.2021 -> count 3)

"Sum Outbound": Contains count of dates from "Outbound table" - column "Outbound date" where the date in "Calender table" equals "Outbound table" & is >= Today (e.g. Outbound date 10.04.2021 -> count 2)

[v-janeyg-msft]

Hi, @dirkkoch 

There is more than one date > today in the inbound date table and outbound date table. How do you get the data of 1, 0 ? It's hard to understand.

Can you put your  sample data on the posti in tabular form instead of screenshoot? It will be convenient for me to write code later. Thanks.

Best Regards

Janey Guo

[dirkkoch]

Hi, @v-janeyg-msft 

I uploaded a .pbix and .xlsx file hoping to better understand the occuring problem:
Power BI – OneDrive (live.com)
The calculation / result I would like to get is shown in the Excel file (yellow marked column).

I hope this helps. Do not hesitate to contact me if you have further questions.

[v-janeyg-msft · Accepted solution]

Hi, @dirkkoch 

You need to create three columns and a measure in calendar table. Because today is the 19th, the data has changed a bit.

Like this:

Column =
VAR a =
    CALCULATE (
        COUNT ( 'Inbound table'[Stock location] ),
        FILTER ( ALL ( 'Inbound table' ), 'Inbound table'[Inbound Date] <= TODAY () ),
        'Inbound table'[Stock location] = "Location 1"
    )
VAR b =
    SUMX (
        FILTER ( 'Calendar table', [Date ] <= EARLIER ( 'Calendar table'[Date ] ) ),
        [ColumnINBOUND]
    )
VAR c =
    SUMX (
        FILTER ( 'Calendar table', [Date ] <= EARLIER ( 'Calendar table'[Date ] ) ),
        [ColumnOUTBOUND]
    )
RETURN
    a + b - c

Measure = IF(MAX('Calendar table'[Date ])<TODAY(),0,1)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[dirkkoch]

Hi @v-janeyg-msft, thank you for the great support. That helped a lot come closer to a final solution. I was furthermore asked to not use a daily but a weekly basis for filtering scale. I added this to the calendar and as a consequence two further questions came up. 

I addressed them with comments in the uploaded .pbix file (Sheets "Visual" & "Data Table").

I know I am asking for a lot, but would you mind take a look at these comments marked in red?

Power BI – OneDrive (live.com)

Thanks again for the great support!

[v-janeyg-msft]

Hi, @dirkkoch 

You have a conflict between the text description and the data, I'm confused, I don’t understand what you want in the end. Can you make it clear? If my answer has solved your initial problem, please cccept it as the solution as encouragement, I will continue to serve you.

Best Regards

Janey Guo

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

[dirkkoch]

Hi @v-janeyg-msft , thank you for the feedback. I added an Excel sheet in the Upload link for better description of the problem. 

Power BI – OneDrive (live.com)

[v-janeyg-msft · Accepted solution]

Hi, @dirkkoch 

You can create a column.

Like this:

Column =
VAR a =
    MAXX (
        FILTER (
            'Calendar table',
            [Relative Week] = EARLIER ( 'Calendar table'[Relative Week] )
        ),
        [Date ]
    )
VAR b =
    MAXX ( FILTER ( 'Calendar table', [Date ] = a ), [Total Preview Day] )
RETURN
    b

Best Regards

Janey Guo

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

[dirkkoch]

Hi @v-janeyg-msft , wow worked just great. Thanks a lot! 

Last but not least how do I manage to show the corresponding value from calculated column in the card visual according to set filter in Preview (in weeks) as described in the Excel file?

Currently I can only manage to show the sum, average, max, min, etc.

[v-janeyg-msft · Accepted solution]

Hi, @dirkkoch 

You need to create a measure,then put it to the card visual. 

Like this:

Measure 2 = var a=MAX('Calendar table'[Relative Week])
return CALCULATE(MAX('Calendar table'[Column]),'Calendar table'[Relative Week]=a)

If you can give me kudos as a reward, I will be very happy.😊

Best Regards

Janey Guo

[dirkkoch]

@v-janeyg-msft excellent. I could not have done it without your great support. Thanks a lot!
Now I know whom to contact if I should ever experience any problem with DAX in PBI