Forum Discussion

Niels_T's avatar
Niels_T
Post Patron
5 years ago
Solved

How to fix this YTD function

I have a YTD function that is forced, meaning that it won't repeat the same number when the YTD function can't find a new value (see image bellow). 

 

The problem here is that as you can see my YTD (Forced) doesn't include the most recent data of April and therefore not really making it YTD as it only adds the data at the end of each month. 

 

 

Here is my YTD (Forced) function: 

 

YTD Forced = 

var _max = today()
return
if(max('Calendar'[Date])<=_max, calculate(Sum('Credit Memo + Invoice'[Amount]),DATESYTD('Calendar'[Date])), blank())
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Niels_T 

    According to your description, I can clearly understand your requirement, you want to get the YTD till today but the data of this month disappeared, right? I think you can try this measure:

    This is the test data I created based on your DAX formula:

    YTD Forced =

var _value=

CALCULATE(SUMX(FILTER('Credit Memo + Invoice',[Date]<=TODAY()),[Amount]),DATESYTD('Calendar'[Date]))

return

IF(

    YEAR(MAX('Calendar'[Date]))<YEAR(TODAY()),_value,

    IF(

        MONTH(MAX('Calendar'[Date]))<=MONTH(TODAY()),_value,BLANK()))

     

    Then create a table chart to place this measure like this:

     

    And you can get what you want.

    You can download my test pbix file below

     

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

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

3 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    Hi, Niels_T 

    According to your description, I can clearly understand your requirement, you want to get the YTD till today but the data of this month disappeared, right? I think you can try this measure:

    This is the test data I created based on your DAX formula:

    YTD Forced =

var _value=

CALCULATE(SUMX(FILTER('Credit Memo + Invoice',[Date]<=TODAY()),[Amount]),DATESYTD('Calendar'[Date]))

return

IF(

    YEAR(MAX('Calendar'[Date]))<YEAR(TODAY()),_value,

    IF(

        MONTH(MAX('Calendar'[Date]))<=MONTH(TODAY()),_value,BLANK()))

     

    Then create a table chart to place this measure like this:

     

    And you can get what you want.

    You can download my test pbix file below

     

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

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

  • HarishKM's avatar
    HarishKM
    Super User
    5 years ago

    Niels_T  Hey Mate try these Dax

    Sales of 2 Product = SUM(financials[COGS]) + SUM(financials[ Sales])
    YTD of 2 product = CALCULATE([Sales of 2 Product],DATESYTD(financials[Date]))
    LY YTD =
    VAR _LY =
    CALCULATE ( [Sales of 2 Product], SAMEPERIODLASTYEAR ( financials[Date] ) )
    RETURN
    CALCULATE ( _LY, DATESYTD ( financials[Date] ) )

     

     

    • Niels_T's avatar
      Niels_T
      Post Patron
      5 years ago

      Unfortunately this is not what I am looking for. The first measure is a standard YTD where the value gets repeated. 

       

      The LY YTD gives me also very different results and also does not stop till this date with showing the values.