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] )
)

     

bilalkhokar73's avatar
bilalkhokar73
Helper 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

Fowmy's avatar
Fowmy
Super User
5 years ago

bilalkhokar73 

So you want to show sales by year but on each year, the amount should be shown only from 1st Jan to the date and month of the lastest year date and month. create the following formula, make sure you have all required columns are there in your dates table, add the years and the total sales and this measure to the visual and check.

 

Salessameperdiod  =

VAR __lastsalesdate =
    CALCULATE ( MAX ( Sales[datei] ), REMOVEFILTERS () )
VAR __day =  DAY ( __lastsalesdate )
VAR __month = MONTH ( __lastsalesdate )
VAR __filterdate = DATE ( MAX ( tm_date[Year] ), __month, __day )
RETURN
    CALCULATE (
        SUM(sales[amt]),
        tm_date[Date] <= __filterdate,
        REMOVEFILTERS ( tm_date),
        VALUES ( tm_date[Year] )
    )

 

 



  • bilalkhokar73's avatar
    bilalkhokar73
    Helper IV
    5 years ago

    I used thsi one but i got an error that 
    Calculation error in measure 'sales'[MEASUREYEARTILL]: An argument of function 'DATE' has the wrong data type or the result is too large or too small.

     


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

    • Fowmy's avatar
      Fowmy
      Super User
      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] )
)

       

      • megane123's avatar
        megane123
        Frequent Visitor
        2 years ago

        YOU ARE MY HERO. I have spent 7 hours today trying to get this to work with no success, this is exactly what I needed! Thank you lol 😀

    • bilalkhokar73's avatar
      bilalkhokar73
      Helper IV
      5 years ago

      Fowmy It's working , made my day , thank you , appreciate