YTD Measure that calculates until today (not beyond)

[thhmez7]

Hello,

I need a YTD measure that calculates the revenue of a year only until the actual day (today) and not beyond if there are data.

My measure still calculates til end of year, whether it's displayed on a table or in a card.

In this example the measure's result should be 1.389.000, because today is Oct 29th and the last date with revenue is Oct 28th, given that I selected year 2020.

Thank you a lot!

[v-luwang-msft]

Hi @thhmez7 ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[Ashish_Mathur]

Hi,

Ensure you have a Calendar Table with columns for Year, Month name and Month number.  Sort the Month Name by the Month number.  To your slicer, drag the Year from the Calendar Table.  To your visual, drag the Month name or date from the Calendar Table.  Write these measures:

Revenue = sum(project[actual revenue])

YTD revenue till date = calculate([revenue],datesbetween(calendar[date],minx(allexcept(calendar[year]),calendar[date]),today()))

Hope this helps. 

[v-luwang-msft · Accepted solution]

Hi @thhmez7 ，

When you use silcer ,slicer only filter the vusual ,and not work on the measure you created.

You could try the following measure:

Actual Revenue YTD3 = 
VAR day1 =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR v1 =
    CALCULATE (
        SUM ( Project[Actual Revenue] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= day1 )
    )
RETURN
   IF(MAX(Project[date])<>BLANK(), IF (
        MAX ( 'Date'[Date] ) <= day1 ,
        CALCULATE (
            SUM ( Project[Actual Revenue] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        ),
        v1
    ),BLANK())

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[thhmez7]

Hi Thank you for replicating this case. I tried your calculation. But still doesn't show the result of yours. Can you look into it?

[v-luwang-msft]

Hi @thhmez7 ，

The following is relationship in  my case .

And could you pls share your pbix file through a public link  that I could test on your sample? Remember to remove confidential data.

Best Regards

Lucien

[amitchandak]

@thhmez7 , Try like these with help from date tbale

YTD Corrected =
var _max = format(today(),"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),format('Date'[Date],"MMDD")<=_max)

LYTD Corrected =
var _max = format(today(),"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),format('Date'[Date],"MMDD")<=_max)

[Nicole_PBI]

Have a look at this post: TOTALYTD until TODAY .