Forum Discussion

bilalkhokar73's avatar
bilalkhokar73
Helper IV
5 years ago
Solved

Dynamic Measure(Year Till date)

In clustered Graph I want to display a single measure which will show me sales of 2019 ,2020 & 2021. here I need sales till date this year , till date last year and till date last to last year so Th...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    5 years ago

    bilalkhokar73 

    Can you try it:

    MEASUREYEARTILL  = 

VAR __lastsalesdate = CALCULATE(MAX(sales[datei] ),REMOVEFILTERS())
VAR day1 = DAY(__lastsalesdate)
VAR MONTH1 = MONTH(__lastsalesdate)
VAR __filterdate = DATE(MAX (tm_date[year_id]), MONTH1, day1 )
RETURN
	CALCULATE (
		SUM(sales[amt]),
		tm_date[Date] <= __filterdate,
		REMOVEFILTERS ( tm_date),
		VALUES (tm_date[year_id] )
)

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

Ensure that you have a Calendar Table with a relationship from the Date column of your Sales table to the Date column of your Calendar Table.  The last date on the Calendar Table should be dynamic enough to go up until the last date in your Sales data table.  In the Calendar Table, write a calculated column formula to extract the Year.  To your visual, drag the Year from the Calendar Table.  Write these measures

Revenue = sum(Data[Sales])

Revenue YTD = calculate([revenue],datesytd(calendar[date],"31/12"))