cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NL-Nicolas
Helper I
Helper I

DAX for Filtering a row of currency by a Date Created by a Measure

Hi Power Bi Community!

 

Below is a screenshot of what I am trying to achieve, I have 2 tables using the same NetSales column. In the car above I want to show the difference for each row between both tables NetSales Column. I have tried so many different things and using 2 different tables to show the % change by date was all I had left for ammo! Recommendations on how to do This Week vs This Week Last Year welcome

 

NLNicolas_0-1659361537113.png

 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @NL-Nicolas ,

1.Please try to add an index column for two table:

Table1:

vbinbinyumsft_0-1659605505314.png

 

Table2:

vbinbinyumsft_1-1659605521854.png

 

2.add a slicer for with, and create a measure and add it into card visual

Differ =
VAR cur_index =
    SELECTEDVALUE ( Table2[Index] )
VAR cur_table1 =
    CALCULATE ( MAX ( Table1[NetSales] ), Table1[Index] = cur_index )
VAR cur_table2 =
    CALCULATE ( MAX ( Table2[NetSales] ), Table2[Index] = cur_index )
RETURN
    cur_table1 - cur_table2

vbinbinyumsft_2-1659605624987.png

Please refer attached .pbix.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-binbinyu-msft
Community Support
Community Support

Hi @NL-Nicolas ,

1.Please try to add an index column for two table:

Table1:

vbinbinyumsft_0-1659605505314.png

 

Table2:

vbinbinyumsft_1-1659605521854.png

 

2.add a slicer for with, and create a measure and add it into card visual

Differ =
VAR cur_index =
    SELECTEDVALUE ( Table2[Index] )
VAR cur_table1 =
    CALCULATE ( MAX ( Table1[NetSales] ), Table1[Index] = cur_index )
VAR cur_table2 =
    CALCULATE ( MAX ( Table2[NetSales] ), Table2[Index] = cur_index )
RETURN
    cur_table1 - cur_table2

vbinbinyumsft_2-1659605624987.png

Please refer attached .pbix.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@NL-Nicolas , With help from a week rank column in date/week table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

You can  measures like
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors