[HELP] YTD over the Years and MTD over the months

[tiago]

Hi there,

I’ve read the forums for couple hours trying to find this but the solutions I’ve found didn’t work.

Total Sales = SUM(Sales[SALE])

(works)

Total Sales YTD = TOTALYTD(Sales[Total Sales],'Sales'[DATE])

(When I do this for some reason I get the same value as Total Sales)

Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('date'[date]) (It just returns the Last Year YTD for 2017, I needed for all other years)

I'am using direct query and I am using as source a view that I've created on SQL the name of the VIEW is 'Sales'. It basically have all sales grouped by day for the last 7 years. What I wanted to archive is to have a dynamic field that will compare all years YTD and I would like to do the same thing for MTD.

Something like this

Sales YTD (01/01 - 19/06)
2012	$     1.000.000,00
2013	$     1.125.000,00
2014	$     1.250.000,00
2015	$     1.375.000,00
2016	$     1.500.000,00
2017	$     1.625.000,00
2018	$     1.750.000,00
Sales MTD (01/06 - 19/06)
jun/12	$           80.000,00
jun/13	$           85.000,00
jun/14	$           90.000,00
jun/15	$           95.000,00
jun/16	$        100.000,00
jun/17	$        105.000,00
jun/18	$        110.000,00

Any Ideas how to do this on PBI ?

Regards,

Tiago

[Anonymous]

I recommend building measures as they turn into a field and you should have no trouble with charts.

First, you will need to incorporate a Date/Calendar table. That's a completely different topic if you do not have one. 

Then, you will need to make an aggrigation Measure for any numbers you want to show Prior Periods. 

In Example, I have Sales in the Database. I need to make an aggrigation measure of Sales from our "Order_Line_Invoice" table.

SalesSUM = SUM(Order_Line_Invoice[Sales])

Measure for Prior Year is created by this:

SalesPY =
CALCULATE (
Order_Line_Invoice[SalesSum],
SAMEPERIODLASTYEAR(Dates_INV[Date]))

I can then pull in the current YTD and pull in SalesPY and get that column results that move with the current time frame being reported.

BONUS: I assume you may want to show change from prior year. Here you go (using my actual code):

SalesPY_Change = IF(ISBLANK([SalesPY]), BLANK(),[SalesSUM]-[SalesPY]) 

This addresses a null value in the prior year and will not show the change then. If you want that you will need to remove the "BLANK" function from the formula. leaving SalesPY_Change = [SalesSUM]-[SalesPY]) 

Here is the location of such DAX Functions:

https://msdn.microsoft.com/en-us/query-bi/dax/time-intelligence-functions-dax

It seems like alot but remember: once a measure is created it becomes very useful as it reads the dates for you (instead of creating a hard coded time reference).  I make report templates that are then used for queries and reports. That template contans many Measures and aggrigation functions making ad hoc reporting fast and FUN TOO! Enjoy.

[Anonymous]

The YTD kind of measure works for one year , so for each year ( FOr current year, last year, previous to last year etc ) you should have a seperate measure. The below idea may help you.

Sales_YTD(2018)=CALCULATE(SUM(Sales_Fact.Sales),DATESYTD(Dates[Date]))

Sales_LastYear=CALCULATE(SUM(Sales_Fact.Sales),DATEADD(Dates[Date],-1,YEAR) // this will be used below.

Sales_LYTD(2017)=CALCULATE(Sales_LastYear,DATESYTD(Dates[Date]))

Sales_LLYear_2016=CALCULATE(SUM(Sales_Fact.Sales),DATEADD(Dates[Date],-2,YEAR) // this will be used below.

Sales_LLYTD(2016) = CALCULATE(Sales_LLYear_2016,DATESYTD(Dates[Date]))

The last 2 steps should be repeated for the earlier years. Same logic applies to MTD as well.

Thanks
Raj

[tiago]

Hi @Anonymous

Thanks for the help, I will give it a try.

Still wonder how with 6 diffrent measures how i will be able to show a simple graph with sales evolution.

I wanted to do something automatic, that after each day it would calculate differently. Like a date filter for all years. from 1st of January until today() -1, something like this is possible ?

And try to do the samething for the MTD. on the current month get from the 1st day of the month until today()-1 and get all previous years...

[tiago]

Hi guys, 

Is there a way to do something like a I said on my previous menssage? Instead of creating mutiple measures to create a filter formula to get the dates form 1st of January until today()-1 for all years. 

Regards,