Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bilalkhokar73
Helper IV
Helper IV

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 That I can compare my sales.

today's date is -04june2021 so i need sales 1 jan to 4june 2019 , 1 jan to 4 june 2020 and 1 jan to 4 june 2021.
I tried with same period formula but it is giving me full year sales of previous year
I have date table and my transaction table has entry from 2019 to till date.

1 ACCEPTED SOLUTION

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Fowmy
Super User
Super User

@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.



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

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

 

Fowmy_0-1622902110776.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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 It's working , made my day , thank you , appreciate

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors