Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

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...
Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    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's avatar
      Mikelytics
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      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