Previous Year To Date not Showing if no Current YTD

[Anonymous]

Hello, 

I'm doing a comparison on Sales figures YTD against the same period the previous year. If I choose to show all clients I only see the clients that have sales in this year and it shows the Previous Year to Date, however I want to see all Clients, even if they had sales last year but none so far this year.

The current set up shows the full previous year to date spend in the total column but just doesnt list the individual clients.

Here are my measures....

• YTD Sales GBP = CALCULATE(sum('All Jobs for Client'[Client_HireTotalNet_GBP]),DATESYTD('Calendar'[Date]))
• PYTD Sales GBP = CALCULATE([Total GBP Net],DATEADD(FILTER(DATESYTD('Calendar'[Date]),'Calendar'[Date]<=LASTNONBLANK('Calendar'[Date],[Total GBP Net])),-1,year))
• Total GBP Net = sum('All Jobs for Client'[Client_HireTotalNet_in_GBP])

Any help greatly received. I'm a beginner at this so not overly familiar with DAX and more complicated measures

[Greg_Deckler]

I'm not entirely sure I understand the problem but I'm thinking maybe:

YTD Sales GBP = 
VAR __value = CALCULATE(sum('All Jobs for Client'[Client_HireTotalNet_GBP]),DATESYTD('Calendar'[Date]))
RETURN
IF(ISBLANK(__value),0,__value)

PYTD Sales GBP = 
VAR __value = CALCULATE([Total GBP Net],DATEADD(FILTER(DATESYTD('Calendar'[Date]),'Calendar'[Date]<=LASTNONBLANK('Calendar'[Date],[Total GBP Net])),-1,year))
RETURN
IF(ISBLANK(__value),0,__value)

Total GBP Net = 
VAR __value = sum('All Jobs for Client'[Client_HireTotalNet_in_GBP])
RETURN
IF(ISBLANK(__value),0,__value)

[Anonymous]

Hi,

I have a list of 5 State Financial years ranging from 2013 to 2018. I am able to calculate difference between two consecutive years(2017,2018) and difference between current year 2018 with any previous year but not able to calculate difference for previous years like (2015,2017)

The DAX functions I used here are:

YTD= Total YTD([Value], (Date), “2018/6/30”)
PYTD = Calculate([YTD], Datesbetween(Date), Date(2013,7,1), Date(2017,6,30)))

Can someone assist me here? Thanks!

[Anonymous]

Hi and thanks for your response.

Ive included a screenshot of the table below, Client names blacked out obviously but as you will see only clients which have sales YTD are listed. The PYTD amounts do not add to the grand total at the bottom, therefore there are no 0's showing in the YTD Sales Column.

I appreciate your suggestion but not clear on the "VAR _value" parts.... is this what I should actually use? as I said Im new to this and unfamiliar with DAX... the original columns were picked up from other forum posts.

[Greg_Deckler]

Yes, that is what you actually use VAR and RETURN are used together. VAR creates a variable. Once you use VAR, you have to use RETURN to actually return a value from the calculation.

Also, looks like you have a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

thanks for the explanation but not quite what I was looking for.

Table still not showing the PYTD sales for Clients which do not have any sales YTD this year.

I will have a read through yout other posts and see if I can solve from there.

Thanks.

[Greg_Deckler]

You might also find this useful. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...