Put Data in Correct Bucket

[Ritesh_Air]

Hi,

I am running into issue where data gets into the wrong bucket when I have monthly filter on.

Sales values comes correctly when there is no monthly filter and I have just yearly data.

For e.g.: This is my yearly data which is correct.

PPY || PY || CY

$20 || $40|| $30

PPY: Prior Prior Year
PY : Prior Year
CY : Current Year

If I put monthly filter in the mix then it goes out of whack in certain situations.

Here is the scenerio:

My year starts on Oct and ends Sept of next year. 

When I put Sept as filter and if there is no value for Sept in CY as it is the last month of the financial year and it hasn't happend yet, it puts the values in the CY bucket.

For e.g. : If Net sales of Sept was $12

then it puts as:

PPY || PY || CY

       ||      || $12   

but it should have been in the Prior Year bucket.

PPY || PY || CY

       ||$12||

How should I force it to remember that this Sept belongs to the PY and it shouldn't put it as the MAX year value which is CY?

I should point out, I am using Matrix visual and the heading column of Values is hard coded as : 

PPY : Net Sales 2018

PY: Net Sales 2019

CY : Net Sales 2020

Now if I put Oct in the mix, then as I already have data for CY Oct, then I get values put to correct buckets.

For e.g.: If Oct is $15 and now Sept and Oct both are selected

PPY || PY || CY

       ||$12 || $15

This is my formula for PPY, PY and CY

Net Sales CY =
var _Cropyear = MAX('Date'[Crop Year Key])
return
CALCULATE([Net Sales], 'Date'[Crop Year Key] = _Cropyear)

Net Sales PY =
var _Cropyear = MAX('Date'[Crop Year Key]) - 1
return
CALCULATE([Net Sales], 'Date'[Crop Year Key] = _Cropyear)

Net Sales PPY =
var _Cropyear = MAX('Date'[Crop Year Key]) - 2
return
CALCULATE([Net Sales], 'Date'[Crop Year Key] = _Cropyear)

Thanks

[amitchandak]

@Ritesh_Air , How are you looking at data is important. Other than YTD it you calendar which will decide

Like

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2nd Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
2nd last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,Year)))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))


last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

A year and two years behind measure should work.

In YTD you can give end date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"9/30"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"9/30"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"9/30"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/