Forum Discussion
Administratoraccumulated to fixed date
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
6 Replies
- Mikelytics
Resident Rockstar
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.
-----------------------------------------------------
Syndicate_AdminThank 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
- Mikelytics
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
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_ValuePlease 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.
-----------------------------------------------------
v-zhangtiCommunity Support
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.
