Prior year sales based on slider

[samhunter]

Hi

I'm trying to show sales of customers for current period and the same period of the previous year based on a date slider.

	Range	Range prior year
Customer1	50	45
Customer2	4	78
Customer3	478	55
Customer4	200	100

The following measure gives me the figures I'm expecting when only displaying total billing qty in a table

Range prior year =

VAR minDate =

Date(year(MIN('processed_cna_mwiauk DimDate'[Date]))-1,month(MIN('processed_cna_mwiauk DimDate'[Date])),day(MIN('processed_cna_mwiauk DimDate'[Date])))

VAR maxDate =

Date(year(MAX('processed_cna_mwiauk DimDate'[Date]))-1,month(MAX('processed_cna_mwiauk DimDate'[Date])),day(MAX('processed_cna_mwiauk DimDate'[Date])))

RETURN

CALCULATE(

SUM('processed_cna_mwiauk FactInvSalesTransaction'[Billing Qty]),

FILTER(

ALL ('processed_cna_mwiauk FactInvSalesTransaction'),

'processed_cna_mwiauk FactInvSalesTransaction'[Billing Date] >= minDate

&& 'processed_cna_mwiauk FactInvSalesTransaction'[Billing Date] <= maxDate))

but when I add the customer I get the below error

I'm using a similar measure for the current range which is working as required below (when including customer in table) so it appears as though my variable definitions are causing the problem but cannot work out what I have done wrong.

Range =

VAR minDate =

MIN('processed_cna_mwiauk DimDate'[Date])

VAR maxDate =

MAX('processed_cna_mwiauk DimDate'[Date])

RETURN

CALCULATE(

SUM('processed_cna_mwiauk FactInvSalesTransaction'[Billing Qty]),

FILTER(

ALL ('processed_cna_mwiauk FactInvSalesTransaction'),

'processed_cna_mwiauk FactInvSalesTransaction'[Billing Date] >= minDate

&& 'processed_cna_mwiauk FactInvSalesTransaction'[Billing Date] <= maxDate))

This is the model I'm using. The tables are all linked via keys

Any help would be greatly appreciated

[v-yingjl]

Hi @samhunter ,

Seems like power bi could not distinguish the context in it. Here is the similar issue that you can refer:

An Argument of Function Date has wrong data type or result is too small or too large 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samhunter]

Hi v-yingjl

Thanks for getting back to me. That does look like a very similar issue.

One solution to giving the variables context was to add the MIN/MAX which I already have in my measure so unfortunately this doesn't help me.

My measure works if I do not include the customer from DimAccount table. Oddly it also works if I use the account key from the Fact table so I think this must be something to do with the join.

I have tried flattening the data into one table and again this works but I now lose the flexibilty I had with the star schema

[amitchandak · Accepted solution]

@samhunter , This should be done with help from date table, slicer should be there on date table

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

or  a measure like

1 year behind =
var _max1 = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min1 = Minx(ALLSELECTED('Date'),'Date'[Date])
var _max = date(Year(_max) -1, month(_max), day(_max))
var _min = date(Year(_min) -1, month(_min), day(_min))

return
calculate([Episodes], filter(all(Date), Date[Date] >=Min && 'Date'[Date] <=Max ))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

[samhunter]

using 'Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))' and setting the schema table join Cross filter direction to Single solved it.

Many thanks

[samhunter]

Hi amitchandak

Thank you for your suggestions. I think I need the measure option to be able to use the slider but it looks the same as the measure I have the problem with. I did try switching the filter to the date table rather than Fact but am still getting the same error. Is there something I am missing?

Thanks for the date table suggestions (marked as date and join on date rather than key). I have made those changes.