Forum Discussion

bilalkhokar73's avatar
bilalkhokar73
Icon for Helper IV rankHelper 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] )
)

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

bilalkhokar73 

You are on the right track with the SAMEPERIODLASTYEAR function. but you need to insert a date filter on the reports and filter the data range from 1/1/2021 to 14/1/2021.

Let me know if this helps.



bilalkhokar73's avatar
bilalkhokar73
Icon for Helper IV rankHelper IV
5 years ago

I use this, in my date tabel i have netry from 2019 to till date as my transaction data is from 2019

Salessameperdiod way2 =
VAR DataMaxDate =
CALCULATE ( MAX (tm_date[Date]), ALL ( Sales[datei] ) )
RETURN
CALCULATE (
[YTD2021],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( tm_date[Date]),
DATESBETWEEN ( tm_date[Date], BLANK (), DataMaxDate )))) 

 

and for 

YTD2021 = TOTALYTD(SUM(sales[amt]),tm_date[Date])
but this one givign me full year sales of 2019,2020 which is wrong 

but I need 2019 sales from 1st jan to 04june 2019 , 1st jan to 04june 2020jan to 04june 2021, i need one dynamic filter so i can put in graph and compare 2019 till date, 2020 till date ,2021 till date