cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Comparing current vs last year sales - multiple date columns

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!

1 ACCEPTED SOLUTION
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
14 REPLIES 14
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

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

Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Hi @v-jayw-msft.

The measures didn't worked for me.

Thank you!

Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

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!

Super User
Measure1 = SUM([Count])

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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?

Super User

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])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Super User

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!

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Super User
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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors