Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Comparing current vs last year sales - multiple date columns

Hello,

 

I have a sales table that looks as follows:

 

Sales table.PNG

 

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

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.

View solution in original post

14 REPLIES 14
v-jayw-msft
Community Support
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.

5.PNG6.PNG

 

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

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

 

4.PNG5.PNG

 

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.

 

Can you please share your pbix file?

 

Thank you!

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!

 

 

Greg_Deckler
Super User
Super User

Measure1 = SUM([Count])

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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?

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

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!


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

 

Can you please check?

 

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?

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

 

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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