Search code examples
powerbidaxmeasure

Create 2 measure for fact tables

I have 3 tables: one for shipments with a field with Production Date, that is linked to a Calendar Table (field date). The calendar table is linked also to a return table (field Return date) and have also an inactive relationship with Return table (Production date) as below.

I would create a table with a measure where Quantity shipped for a specific year is 0 for previosus years and is the overall value for the specific year and next

I try to explain better in the image below. For example in year 2020 I shipped 2500 units the measure show 0 for 2018 and 2019 and 2500 for 2020 up 2023

I created

QSpedita = 
    CALCULATE(
        [Quantità spedita], ('Calendar Lookup'[Year]=2018)
    )

But it works only for 2018 and I should need to create different measure for each year, instead I need a formula that recognize the filter context. Do you have any suggestion?

Thank you

I try to debug the formula created by Amira and I have:

QuantityShippedAdjusted = VAR CurrentYear = MAX('Spedizioni'[anno]) VAR ProductionYear = MAX('Calendar Lookup'[Year]) RETURN CurrentYear and now I receive , where for example the first row have all 2018 and not 2018 2019 and so on

enter image description here

enter image description here

enter image description here

Production Year 2018    2019    2020    2021    2022    2023
2018            5000    5000    5000    5000    5000    5000
2019            0       7000    7000    7000    7000    7000
2020            0       0       2500    2500    2500    2500
2021            0       0       0       5000    5000    5000
2022            0       0       0       0       2750    2750
2023            0       0       0       0       4125    4125
Solution

  • You can use a variable to store the year from the current filter context, which is determined by the calendar table's year attribute and another one to store the production year from the shipments table.

    Then you check if the ProductionYear is greater than the CurrentYear :

    A/if true, it means the year being evaluated is before the production year, so it returns 0.

    B/if less than or equal to the CurrentYear, you calculate the sum of Quantity Shipped for all years from the production year up to and including the current year.

    You can see that the ALL() removes any existing filters on the calendar table and the FILTER condition inside the CALCULATE function will guarantee that only the years from the production year to the current year are included in the sum.

     QuantityShippedAdjusted = 
    VAR CurrentYear = MAX('Calendar Lookup'[Year])
    VAR ProductionYear = MAX('Shipments'[Production Year])
    RETURN
        IF(
            ProductionYear > CurrentYear,
            0,
            CALCULATE(
                SUM('Shipments'[Quantity Shipped]),
                FILTER(
                    ALL('Calendar Lookup'),
                    'Calendar Lookup'[Year] >= ProductionYear && 'Calendar Lookup'[Year] <= CurrentYear
                )
            )
        )