cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Re: How to display sales YTD and sales last YTD over years?

Can you display the difference of this year ytd and last year ytd .
 
Difference  of 2 YTDS = YTD of this year - YTD of Last Year. 

i am getting error.

please solve this  

Thanks 

sandeep 

khanashique Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Hi Angela,

 

Liked the explanation, its in detail... but what if i do not have date column and i have only month and year coloumn then how do i do this calculation...

 

thanks.

Super User
Super User

Re: How to display sales YTD and sales last YTD over years?

Hi,

If there are 2 columns - one for month and another for Year, then write this calculated column formula

=1*("1/"&[Month]&"/"&[Year])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
khanashique Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Hi Ashish, 

 

Thanks, now i got the date column, can you please help me to calculate LYTD (Last Year to Date) figure...

 

Regards,

Ashik.

Super User
Super User

Re: How to display sales YTD and sales last YTD over years?

Hi,

Create a Calendar Table* and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  In the Calendar Table, extract Year and Month by using the folloiwng calculated column formulas: Year=Year(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm").  Create slicers for Year and Month and select any one Year and Month.  Write this measure

Sales = SUM9Data[Sale])

YTD Sales = CALCULATE([Sales],DATESYTD(Calendar[Date],"31/12")

LYYTD Sales = CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

* To create a Calendar Table, go to Modelling > New Table and write this formula

Calendar=Calendar(MIN(Data[Date]),MAX(Data[Date]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
khanashique Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Hi Ashish, Thanks for your time,

 

I have follow all the steps to calculate the measures, but here i get same figure for all the measures...

 

Sales = samefigure

YTD = samefigure

LYTD = samefigure

 

I have a question in YTD, why have you mentioned "31/12" what is the use of this.

Super User
Super User

Re: How to display sales YTD and sales last YTD over years?

Hi,

Have you chosen a specific month and Year in the slicer? (The Year and Month slicers should get built from the Calendar Table)  "31/12" implies that the Financial Year ends on December 31.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
khanashique Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Please see the PBIX file i have shared in the link...

https://drive.google.com/file/d/1DG1MJ9zEbzFPy7d0C73NSbDW6dwpkbSw/view?usp=sharing

Super User
Super User

Re: How to display sales YTD and sales last YTD over years?

Hi,

You had not created a relationship.  After creating it, the result seems correct.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
khanashique Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Thanks Ashish,

 

I did missed that, now when i make the relation between the two table, i get correct figure but there is one thing that last year figure in the table i don't get. it shows only current year figure... and i believe this is due to the selection we made...

https://drive.google.com/file/d/1T_ZbDf-w8DMkyPMhJuu7IOYmXNMnTPBZ/view?usp=sharing

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 289 members 2,958 guests
Please welcome our newest community members: