How to create a YTD aggregation as a calculated column in Power BI ?

[oscarca]

Hello Community,
I am trying to create a calculated column in a calculated table that aggregates Invoiced Quantity year to date for each Period in the table. So for example Period 202307 should summarize the values between 202301 and 202307 and 202308 should summarize the values between 202301 and 202308 etc. But I don't seem to get the logic to work quite properly because If I choose the Period 202307 it will only return the value for that month and not the sum of 202301 - 202307.
My example consists of one fact table "Fact_sales" and two dimension tables "Dim_Calendar" and Dim_Item:

CalculatedTable = 

SUMMARIZE (

    Fact_sales,

    Dim_Calendar[Fiscal period],

    Dim_Calendar[Fiscal year],

    Dim_Customer[Customer],

    Dim_Item[Item number],

    "YTD_InvoicedQty",

        CALCULATE (

            SUM ( Fact_sales[Invoiced quantity - basic U/M] ),

            FILTER (

                ALL ( Dim_Calendar ),

                Dim_Calendar[Fiscal year] = VALUES(Dim_Calendar[Fiscal year]) &&

                Dim_Calendar[Fiscal period] <= MAX(Dim_Calendar[Fiscal period])

            )

        )

)

All help are appreciated!
Best regards,
Oscarca

[Ashish_Mathur]

Hi,

Why are you even attempting to solve this with a calculated column?  Why not with a measure?

[lbendlin]

Have you considered using a Quick Measure for this?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...