Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Syndicate_Admin
Administrator
Administrator

accumulated 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 6
v-zhangti
Community Support
Community 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.

Mikelytics
Resident Rockstar
Resident Rockstar

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?

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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.

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

 

Mikelytics_1-1670791858290.png

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.

Mikelytics_2-1670791919607.png

 

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

Mikelytics_3-1670792070466.png

 

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:

Mikelytics_4-1670792177009.png

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

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Mikelytics
Resident Rockstar
Resident Rockstar

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.