Comparing current vs last year sales - multiple date columns

[Anonymous]

Hello,

I have a sales table that looks as follows:

How can I calculate the columns Count 2020, Count 2019, by using the As of date column as a filter in a visual?

Ej: As of date: 2/1/2020 vs. 2/1/2019

Thank you!

[v-jayw-msft]

Hi @Anonymous ,

Try

C_Y = SUM('Table'[count])

L_Y = CALCULATE(SUM('Table'[count]),FILTER(ALL('Table'),'Table'[As of date]=EDATE(SELECTEDVALUE('Table'[As of date]),-12)))

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft .

My desired output is a table, so I'm getting a fixed value for the L_Y measure for each month.

The table is able to show

 Count 2020= CALCULATE(sum(table[count]),table[sales date]>=date(2020,1,1),table[sales date]<=date(2020,12,31))

values in the output table, for January to December of this year

Thanks

[v-jayw-msft]

Hi @Anonymous ,

Check if the visual below is what you want.

C_Y = CALCULATE(SUM('Table'[count]),FILTER(ALLEXCEPT('Table','Table'[sales date month]),'Table'[as of date] in VALUES('Table'[as of date])))

L_Y = CALCULATE(SUM('Table'[count]),FILTER(ALLEXCEPT('Table','Table'[sales date month]),EDATE('Table'[as of date],12) in VALUES('Table'[as of date])))

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft.

The measures didn't worked for me.

Can you please share your pbix file?

Thank you!

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hello @v-jayw-msft and @Greg_Deckler 

The following worked for me, modifying the code you provided:

Measure 1 = CALCULATE(sum(Table[Count]),Table[Date]>=Date(2020,1,1),Table[Date]<=Date(2020,12,31))

Measure 2 = CALCULATE(sum(Table[Count]),Table[Date]>=Date(2019,1,1),Table[Date]<=Date(2019,12,31),SAMEPERIODLASTYEAR(Table[Date]))

Thank you so much for your help!

[Greg_Deckler]

Measure1 = SUM([Count])

Measure2 = SUMX(FILTER('Table',[As of Date] <= 0DATE(2019,2,1)),[Count])

[Anonymous]

Thank you @Greg_Deckler 

I was able to get the "As of date" field as a visual filter.

How to transform the measure 2, to get the value 1 year before the one I selected on the filter?

[Greg_Deckler]

Measure2 =

  VAR __TheChosenOne = SELECTEDVALUE('Table'[Date]) // this is your slicer

RETURN

  SUMX(FILTER('Table',[As of Date] <= DATE(YEAR(__TheChosenOne) - 1,MONTH(__TheChosenOne),DAY(__TheChosenOne)),[Count])

[Anonymous]

I calculated Count 2020 with the measure (no date table needed so far):

Count 2020= CALCULATE(sum(table[count]),table[sales date]>=date(2020,1,1),table[sales date]<=date(2020,12,31))

If I select 2/1/2020 "As of date" visual filter, Count 2020 works in the output table.

The measure 2, using a data table looks as:

Measure 2 =

VAR TheChosenOne = SELECTEDVALUE('Date'[Date])

return

sumx(filter(table,table[As of date]<=DATE(year(TheChosenOne)-1,month(TheChosenOne),day(TheChosenOne)),table[Count 2020]))

Unfortunately for Measure 2 I got the error: too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.

[Greg_Deckler]

Well, let's match up the parens:

Measure 2 =

VAR TheChosenOne = SELECTEDVALUE('Date'[Date])

return

sumx(

  filter(

    table,

    table[As of date]<=

       DATE(

         year(TheChosenOne)-1,

        month(TheChosenOne),

        day(TheChosenOne)

       )

  ),

  table[Count 2020]

)

Found it!

[Anonymous]

Thank you for checking @Greg_Deckler .

I got the following error in the output table: Can't display the visual:

MdxScript(Model) (133,44) Calculation error in message ('table'[Measure 2]: An argument of function 'DATE' has the wrong data type or the result is too large or too small.

I changed the value type to Whole number, and I'm still getting the error.

Can you please check?

[Greg_Deckler]

Are you getting this with nothing selected in your Date slicer? Probably need to return some date value using the optional parameters for SELECTEDVALUE to return a default?

[Anonymous]

I'm getting the message with both date selected, and no date selected in the slicer.