Measure to calculate sales of prior season only until same day of last year

[Anonymous]

Hi all,

I'm pretty new of PowerBI, so I'm asking you help for something that may be a basic problem, but I tried to solve it without success.

This is my scenario: I have a sales table like this:

Date	Same day Of last Year	Season	Prior Season	Qty
06-03-2020	08-03-2019	SS20	SS19	2
07-03-2020	09-03-2020	SS20	SS19	3
08-03-2019	09-03-2018	SS19	SS18	2
09-03-2019	10-03-2018	FW18	FW17	5
10-03-2019	11-03-2018	SS19	SS18	1

The user should be able to select a time period based on field "Date" and a sales season, and he wants to see the sold Qty for the selected season vs sold quantity of prior sales season, only considering quantities we sold until the same day of last year.

For instance, if he selects:

• Time Period: for January 1st 2020 to March 7th 2020
• Sales Season: SS20

The result shoud be: 

Season	Measure Qty Current Season	Measure Qty Prior Season
SS20	5	2

I was obviously able to create the first measure, it's simple SUM of column Qty, but I don't know how to calculate the second measure, Qty Prior Season.

Basically the measure should filter data based on columns Same day Of last Year and Prior Season: in my example, I have the same day of last year of my selected date is March 9th 2019 and the prior Season is SS19, so the measure should not consider the record with a date after March 9th and a season different from SS19.

Sorry for bother you with this stuff, but, like I said I'm quite new in this magic world.

Thanks for your attention

[Greg_Deckler]

@Anonymous - There are two basic ways of attacking this problem.

One is with the time "intelligence" functions like SAMEPERIODLASTYEAR. You generally wrap your calculation in a CALCULATE and then your filter clause is your time "intelligence" function. You will need a separate, related date table for this to function properly.

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

The other way is to just use filters because that's essentially all time "intelligence" functions are. In that case, 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-TITHW/m-p/434008

[Anonymous]

Thanks Greg for your feedback, it's very helpful!

I tried to follow your suggestions, so I created this measure for prior year same day value:

VAR _MAX_SAME_DAY = MAX ('DTC Cube'[DATE_ID_SAME_DAY])
VAR __TmpTable = CALCULATETABLE('DTC Cube',ALL('DTC Cube'[DATE_ID]))
RETURN SUMX(FILTER(__TmpTable, 'DTC Cube'[DATE_ID]=_MAX_SAME_DAY ),[VALUE])

I didn't use an external table to calculate the variable for the same day, but it seems working.

I have now 2 problems: first of all, the value for prior year is correct for each row, but the total it's not working:

Date	Current Year value	Prior Year value
03/06/2020 00:00	20.899,41	21.205,38
04/06/2020 00:00	23.559,58	34.898,64
05/06/2020 00:00	39.596,26	41.917,59
Total	84.055,25	41.917,59

Basically instead of a total I'me getting the value of the latest day.

The second problem is the following: if I add to the table an additional dimension, for instance "Season", the table becomes like this:

Date	Season	Current Year value	Prior Year value
03/06/2020 00:00		0	21.205,38
03/06/2020 00:00	FW19	2.821,58	21.205,38
03/06/2020 00:00	ND	0	21.205,38
03/06/2020 00:00	SS20	18.077,83	21.205,38
04/06/2020 00:00		0	34.898,64
04/06/2020 00:00	FW19	7.504,60	34.898,64
04/06/2020 00:00	ND	0	34.898,64
04/06/2020 00:00	SS20	16.054,99	34.898,64
05/06/2020 00:00		0	41.917,59
05/06/2020 00:00	FW19	8.366,92	41.917,59
05/06/2020 00:00	ND	0	41.917,59
05/06/2020 00:00	SS20	31.229,34	41.917,59
Total		84.055,25	41.917,59

So the basic current year value measure is correctly split by season, my "complex" one isn't at all (and of course the total is still not a total...).

Is there a way to make it work in both situations?

Thanks a lot!

[v-xicai]

Hi @Anonymous ,

You may change your measure [Prior Year value], and create another new measure to correct the total sum like DAX below.

Prior Year value=

VAR _MAX_SAME_DAY = MAX ('DTC Cube'[DATE_ID_SAME_DAY])

VAR __TmpTable = CALCULATETABLE('DTC Cube',ALL('DTC Cube'[DATE_ID]))

RETURN IF([Current Year value]= BLANK(), BLANK(),  SUMX(FILTER(__TmpTable, 'DTC Cube'[DATE_ID]=_MAX_SAME_DAY ),[VALUE]) )



Prior Year value_new=

var _table = SUMMARIZE('DTC Cube', 'DTC Cube'[Date_ID],"_Value", [Prior Year value] )

return
IF(HASONEVALUE('DTC Cube'[Date_ID]), [Prior Year value], SUMX(_table,[_Value]))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you Amy for your suggestion, I tried to replicate it but I still have some problems.

I've create a pbix file to facilitate our communication, you can download it here:

https://we.tl/t-BYGGXDettS 

I have 2 open point:

1. in your formula there was a "d" after HASONEVALUE function, and I don't know what it means (in my example I put the Current Year Value measure). By the way, I'm not getting the total, sure I'm missing something.
2. As you can see in the second table, I can't make it work when I add another dimension, like Season. Is there a way to get the also the prior year value splitted by season?

My final goal is to filter by date and season and get the value for same day and prior season.

I hope I was clear and thank you again