Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Date | Score |
29/11/2022 | 50 |
30/11/2022 | 20 |
01/12/2022 | 7 |
02/12/2022 | 22 |
03/12/2022 | 15 |
04/12/2022 | 26 |
05/12/2022 | 27 |
06/12/2022 | 27 |
07/12/2022 | 13 |
08/12/2022 | 31 |
09/12/2022 | 32 |
10/12/2022 | 22 |
11/12/2022 | 19 |
12/12/2022 | 45 |
13/12/2022 | 24 |
14/12/2022 | 30 |
15/12/2022 | 42 |
16/12/2022 | 34 |
17/12/2022 | 33 |
18/12/2022 | 33 |
19/12/2022 | 21 |
20/12/2022 | 37 |
21/12/2022 | 10 |
22/12/2022 | 34 |
SELECTED DATE | 20/12/2022 |
20/12/2022 | 37 |
13/12/2022 | 24 |
06/12/2022 | 27 |
29/11/2022 | 50 |
Solved! Go to Solution.
Hi @Mahmed1
See it all at work in the attached file.
1. Create a date table DateT
2. Place DateT[Date] in a slicer to select the date
3. Create this measure
ShowMeasure =
VAR offsetDays_ = 7
VAR diff_ = DATEDIFF ( SELECTEDVALUE ( Table1[Date] ), SELECTEDVALUE ( DateT[Date] ), DAY )
RETURN
IF ( MOD ( diff_, offsetDays_ ) = 0, 1, 0 )
4. Place Table1[Date] and Table1[Score] in a table visual
5. Apply [ShowMeasure] as filter to the table visual and choose to show items when [ShowMeasure] is 1
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
See it all at work in the attached file (Page 1).
If you place both Category and Date in the rows of a matrix visual it will work.
With Date in columns I'm afraid the auto-exist behavior will make the current approach fail.
An alternative that will work though is to create another measure that leverages the one we had earlier. It assumes you want the SUM of the scores; if not, you can update it accordingly:
ScoreMeasure =
VAR aux_ = SUM(Table1[Score])
RETURN
IF([ShowMeasure] = 1, aux_)
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hey
thank you once again for all your help, in the measure, is it possible to limit the data to also only go back say 8 weeks
for eg - if i select 20/12/2022 it works beautifully to go back - 7 days
now i can have years worth of data so would be nice to only go back to say 8 weeks worth so charts doenst look
clustered
is this possible?
You could either
1. Place Date in rows and Category in columns, then you will be able to sort by date or
2. Try something like what they explain here:
https://community.powerbi.com/t5/Desktop/Matrix-Column-Head-Order/td-p/71572
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
See it all at work in the attached file (Page 1).
If you place both Category and Date in the rows of a matrix visual it will work.
With Date in columns I'm afraid the auto-exist behavior will make the current approach fail.
An alternative that will work though is to create another measure that leverages the one we had earlier. It assumes you want the SUM of the scores; if not, you can update it accordingly:
ScoreMeasure =
VAR aux_ = SUM(Table1[Score])
RETURN
IF([ShowMeasure] = 1, aux_)
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
hey
had a quick look and it appears to work beautifully so far
Is it possible to sort table based on date selected ie sort by 20-12 so that the category shows Other 1st?
Thank you
Awesome - thank you for taking time out
i will download this file and take see how this approach works
thank you again
Hey guys - when i put the dates in columns, and categories in rows it doesnt work 😞
The result should be like this (Ive put random numbers in and ranked by the variance column
Can you share the pbix?
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
how do i add an attachment?
You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
Hi I have used your example pbix file and tried to amend that
I will send the example file
Ultimately i want to achiythe snapshot excel view
thank you
Thank you once again
i really appreciate all your help - i have an additional metric i would like to add to this matrix please
i have used your solution and in a matrix put
Category in Rows
Dates in Columns
Scores in Values
this gives me a trend of weekly movement based on the selected date
what i would like to add at the end of the matrix is a % change column that compares the selected date against the previous week date and then ranks the whole dataset based on this column
(selected date/prev weekdate) -1
this way i can have a matrix that shows me movements week on week and then the very last column has a % variance for the selected week against previous (ranked highest to lowest)
how would you approach this?
thanks once again
Guys thank u so much
like i say, i am new to this, I didn't even see your attached files 🙂
if i getting it correctly, in my example
i would create another disconnected calender table ny using min or max of the date table or my tbl_scores table
and then i apply the measure in a filter?
how does the required dates part of the formula work? Does it create a virtual table with a 0 and 1?
You can download the file at the bottom of my first reply.
yes is better to have a new disconnected date table.
please provide some screenshots
Thank you
please see my latest response re additional metric
hopefully you can advise the best approach for this
i know i can compare selected date with last week in its own matrix by using dateadd going back 7 days potentially but wanted to add in 1 matrix visual but if this is not the best approach please advise otherwise
thank you again
Thank you i cant wait to try this
my tables already in my file are
Calender
tbl_Scores
tbl_Area
are you saying first duplicate this calender table?
I have 3 tables connected
Date Table
Scores Table
Area LookUp table
i have standard measures to show me scores for each date and area
do i now create a new disconnected calender table first?
Wow thank you for getting back to me
When you say disconnected date table - do you mean create another date table but have no relationships?
i already have a date table and scores table in my powerbi fil
sorry i am new to this
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
30 | |
15 | |
11 | |
10 | |
9 |