accumulated to fixed date

[Syndicate_Admin]

Hello

I have the date of the last order that has come in. I want to know the accumulated for this period in different years and put it on a card. For example, if the last date is 02/15/2022, I would like the card to show the cumulative sales to 02/15 of 2022 or 2021 or 2020,...., as filtered in the segmenter of the year. How do I create the measure for this card? Thank you

[v-zhangti]

Hi, @Syndicate_Admin 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[Mikelytics]

Hi @Syndicate_Admin 

This is normal behavior Sind ist takes the last date in scope. You said that you selected the year 2021 and you expect the value 15/2. How do you Power BI tell that you expect this date?

or is 15/2 the end of your year?

[Syndicate_Admin]

The date of the last order is 15/02/22. What I want to see on a card is cumulative growth versus the same period last year or previous years. Change the year with a date (year) segmenter. I want to do the same thing per quarter and month. If a new order is added on 16/02/22, I want to see that growth on the card but in the new period.

[Mikelytics]

Hi @Syndicate_Admin 

ok, understoof. As I have no input from your data model I ahve to asume that you have a fact table and a dedicated date table. If you have no date table I highly recommend to implement one in your model.

I built an example with a Fact and a Data Table. Please look at the following Data Model

Then I bult the follwing based formula for the sum of my value in the Table 'Fact Sample'. As you can see the last value is on 07.02.2024.

So based on your requirement ona my understanding you now want to have the yearly values until

07.02.2024, 07.02.2023, 07.02.2022 and so on, right? So for exampel for 2023 this would be 126 for january + 33 until 07.02. 2023 which is 159. AS you see above the value for 2024 would be99+17=116

So I build the following formua to get this:

002 YTD fixed Date = 

var var_LastOrderDate = CALCULATE(LASTDATE('Fact_Sample'[Date]),ALL(DimDate))
var var_SelectedYear = MAX(DimDate[Year])
var var_LastOrderMonth = MONTH(var_LastOrderDate)
var var_LastOrderDay = DAY(var_LastOrderDate)
var var_DateYTD = DATE(var_SelectedYear, var_LastOrderMonth, var_LastOrderDay)

var var_YTD_Value =
CALCULATE(
   TOTALYTD([002 Sum Value],DimDate[Date]),
   ALL(DimDate),
   DimDate[Date] = var_DateYTD
)

RETURN
var_YTD_Value

Please see the result which is the value until 07.02. for each year:

As you can see both values are correct (116 for 2024 and 159 for 2023). I also checked 2022 so the value of 129 is correct.

Best regards

Michael

-----------------------------------------------------

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

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Mikelytics]

Hi  @Syndicate_Admin 

Please check the following formula

TOTALYTD-Funktion (DAX) - DAX | Microsoft Learn

In the first argument you put in the expression like SUM(Table[Column]) and in the second the list of dates like Table[Dates]. You should work with a sepaarte date table which is linked to your fact table.

Best regards

Michael

-----------------------------------------------------

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

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Syndicate_Admin]

Thank you. I had already tried it but when selecting the year 2021 it gives me the accumulated to 12/31/21, when what I wanted is the accumulated to 2/15/21.

Best regards

Aureli