cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skashifz
Frequent Visitor

Need to change this DAX table

Hi guys,

I am very new to Power Bi. So appologies for any mistake or shortcoming in my question.

(Sample pbix file https://www.dropbox.com/s/258z1bv2fnzrq47/AdventureWorksDW1.pbix?dl=0 )

 

CalculateTable1.JPG

 

 

This Measure table is showing all table columns for dates last 14 days (by taking max date). I need to change this to use Slicer date instead of MAX date of the table. So, it will show me 14 days data before any selected date. Also, I need to show only the date and SalesAmount, Just need these 2 columns please. Any idea or hint for me to work on please ?

 

Thanks in advance and appreciate your help.

 

And Note to clarify

I am not trying to change value of calculated column. I am trying to create a new "measure table", The table created in my example is based on the maximum date, Is this possible to create similar measure table based on date seelected from slicer instead of the max date picked from the base table ?

 

If there is a way to get the list of previous 14 dates, then I am sure, there can be a way to also include the corresponding SalesAmounts for each row.

 

My problem is, I dont want one SUM of all at the end, i need individual values, So I can try to draw a line chart for the 14 day window finishing at slicer date.

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @skashifz 

I think you want to build a line chart to show SalesAmounts in previous 14 dates. Try to build an unrelated calendar table and build a measure to filter your visual.

Calendar = 
SUMMARIZE(ALL(DimDate),DimDate[DateKey],DimDate[FullDateAlternateKey])

Measure:

Measure = 
VAR _SelectDate = SELECTEDVALUE('Calendar'[FullDateAlternateKey])
RETURN
IF(MAX(DimDate[FullDateAlternateKey])>=_SelectDate-14&&MAX(DimDate[FullDateAlternateKey])<_SelectDate,1,0)

Add this visual into filter field in line visual and set it to show items when value = 1. Result is as below. When I select 20140128, we will get result from 20140114 to 20140127.

1.png

Best Regards,
Rico Zhou

 

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

3 REPLIES 3
amitchandak
Super User
Super User

@skashifz , You can not change data of calculated column based on slicer value. This means if you change the value of the slicer it will not change.

 

It can only work if the table is var in a measure 

 

example

Meausre  =

var _tab = calculatetable(<code>)

return

sumx(_tab,[A])



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

@amitchandak , thank you very much for the response. I am not trying to change value of calculated column. I am trying to create a new "measure table", The table created in my example is based on the maximum date, Is this possible to create similar measure table based on date seelected from slicer instead of the max date picked from the base table ?

 

If there is a way to get the list of previous 14 dates, then I am sure, there can be a way to also include the corresponding SalesAmounts for each row.

 

My problem is, I dont want one SUM of all at the end, i need individual values, So I can try to draw a line chart for the 14 day window finishing at slicer date.

 

Hope I make sense.

RicoZhou
Community Support
Community Support

Hi @skashifz 

I think you want to build a line chart to show SalesAmounts in previous 14 dates. Try to build an unrelated calendar table and build a measure to filter your visual.

Calendar = 
SUMMARIZE(ALL(DimDate),DimDate[DateKey],DimDate[FullDateAlternateKey])

Measure:

Measure = 
VAR _SelectDate = SELECTEDVALUE('Calendar'[FullDateAlternateKey])
RETURN
IF(MAX(DimDate[FullDateAlternateKey])>=_SelectDate-14&&MAX(DimDate[FullDateAlternateKey])<_SelectDate,1,0)

Add this visual into filter field in line visual and set it to show items when value = 1. Result is as below. When I select 20140128, we will get result from 20140114 to 20140127.

1.png

Best Regards,
Rico Zhou

 

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

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors