Hi, thank you for responding, I kind of get what you are saying, but I am not sure where to make the changes, is it here somewhere? ‘All’ is already there, but I do not think this is what you are referring to. Also, I think the '1’s down the side might be having an effect as well. In the table I put an index and asked PBI to sort using the index, but it does not change the order of the choices.
Regards,
Paul.
Hi Brian,
Thank you for this, I am going to look into this, but I need to understand why what I have done has not worked for important learning purposes, plus it will be nice to fix what have and show people, then ‘upgrade’ to what you have suggested here.
Regards,
Paul.
Hi Paul,
You need to put your [Time Period] measure into the table on your report page, not fields from the date table. Or use a card visual with your measure since it returns only one sales amount anyway. Since you use TODAY as the reference date in your measure code and you define the periods in your measure code as well you cannot set a different date context for this measure. Vice versa your measure does also not affect the date context of your report page, thus your table shows all the dates. You only have to make sure that you do not set a date context that hides days that are needed in your measure or, as mentioned by @AntrikshSharma , explicitly add ALL(‘Dates’) to you measure.
The"1"s in your Filters mean: "There is one occurence of “Last 7 Days” in your ‘Slicer’ table, there is one occurence of “MTD” in your ‘Slicer’ table and so on. This does not affect the behavior of your report, it’s only for information purpose. There is no custom sorting in the Filters pane. You could use a slicer on your report page instead, there sorting works. Or you include 1., 2., 3. … in the name of your options so they sort as you prefer, but then you also need to adjust your Measure code if you refer to the names there.
Ultimately I’d also prefer the approach described by @BrianJ .
BR
Martin
Hi Martin,
Thank you for taking the time to reply, I see what you mean about the single date. I need to start this again. I have created the table that Brian mentioned ( I followed the guide to turn it into a table as well), I do not know any ‘M’, and I am trying to work out what I need to do to set the Australian FY correctly, I have tried several things but it always errors out. It is advanced for me, I’m not sure how to change those things.
I just need to get the date table straight for the Aussie FY (and eventually enrich it with holidays).
Have a slider or radio button option that will change the visuals to show the last week, and another for the last 2 weeks Sunday to previous Monday.
Something like this…
I should have mentioned those goals in the first place, it most likely would have helped you help me, sorry about that. These are the current goals I need to meet, I have read a lot and there is more out there than I know what to do with and I might be suffering with too much information. I was hoping to find something straight forward on Enterprise DNA, I may not be looking in the right place. Brian’s suggestions look solid, but I’m not sure if I know enough to be able to use/employ it effectively. I am determined to get this working one way or another.
Regards,
Paul.
Hi Brian, I just do not understand how this is working. I read Chris’ blog, and all the instructions, when I create the two sets of ‘M’ code, I do not end up with tables, I just end up with…
I"m not getting this, and I’ve lost a day on it. Why am I not getting tables?
Kind regards,
Paul.
Hi @PaulW,
Have you tried using the nested version you can find here
Just paste it in a new Blank Query and the output will be a Period table.
I hope this is helpful
Hi @Melissa , thank you, but the result is exactly the same.
Why is this working for everyone but me? I dont understand the difference.
Regards,
Paul.
Hi @PaulW,
You need to go to Power Query first, follow these steps:
I hope this is helpful
Melissa, that is crazy! Can you tell me why placing it in advanced editor instead of the formula bar makes the difference, please?
Thank you,
Regards,
Paul.
Hi Brian, would you please be kind enough to redo the link for the period slicer approach, please? I have had a really good look for it but I cant find it. I think its just missing the ‘@’.
Thank you.
Regards,
Paul.
Thank you, @BrianJ
‘Looks like you’re making a good day’s worth of progress.’
HA! Yes! But now I have having trouble with the filtering!
Regards,
Paul.
Did you download @greg’s Challenge #5 PBIX file? He has the period filtering set up perfectly with the extended date table there, so it should give you a good example to follow.
I didnt actually, I will now though and check it out. Thanks @BrianJ have a great weekend!
Regards,
Paul.
I have to give up on this, I do not understand why this works for other people but not for me. I have read the blogs several times over, the choosing of the buttons has no effect on any of the visuals in my report.
Regards,
Paul.
Did you actually try what I suggested? Using ALL ( Dates ) also why are you summing Dates?
Time Period =
VAR tod =
TODAY ()
VAR yd =
YEAR ( tod )
VAR md =
MONTH ( tod )
VAR yest = tod - 1
VAR last7 = tod - 7
VAR sele =
SELECTEDVALUE ( Slicer[TYPE] )
VAR todaysales =
CALCULATE ( SUM ( Dates[Date] ), FILTER ( ALL ( Dates ), 'Dates'[Date] = tod ) )
VAR yestsales =
CALCULATE (
SUM ( Dates[Date] ),
FILTER ( ALL ( Dates ), 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
)
VAR last7sales =
CALCULATE (
SUM ( 'Dates'[Date] ),
FILTER ( ALL ( Dates ), 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
)
VAR mtdsale =
CALCULATE (
SUM ( 'Dates'[Date] ),
FILTER (
ALL ( Dates ),
YEAR ( 'Dates'[Date] ) = yd
&& MONTH ( 'Dates'[Date] ) = md
&& 'Dates'[Date] <= tod
)
)
RETURN
IF (
ISINSCOPE ( Slicer[TYPE] ),
SWITCH (
TRUE (),
sele = "Today", todaysales,
sele = "Yesterday", yestsales,
sele = "Last 7 Days", last7sales,
sele = "MTD", mtdsale
)
)The best way to get a solution from the forum is always to post a PBIX file. Here’s a video on a simple approach to anonymizing and reducing your data for posting on the forum. I’m 100% confident that if you can provide a file representative of your data and data model that we can work out what the problem is.
Hello, I didn’t realize I was summing dates, I used the code provided. If I saw the summing of dates, my lack of knowledge would have to have assumed it was correct. I did look into the ALL (Dates), but I was not too sure what you meant, I thought you meant all dates in the filter which I already had, so perhaps I misunderstood. Well, I most certainly must have!
Regards,
Paul.
Hi Brian,
Ah ok, I’ve only just started using the forum, so I guess I also need to learn how to use it as well, I’ll look at the video, to be honest, I do not have full confidence in the data I am importing as its a bit patchy, so I am in a position of not knowing if its the underlying data or the slicer. I will watch the video and do the needful and upload, there may be many mistakes, I am no completely sure I have the modelling correct either.
Thank you all for your help so far, I really appreciated it enormously, the learning curve is steep but I am enjoying the climb!
Regards,
Paul.
“Ah ok, I’ve only just started using the forum, so I guess I also need to learn how to use it as well”
You’re doing absolutely fine. To be a good citizen on the forum, you just need to do three things:
Give your problem an honest effort before asking the question (search the forum and the knowledge base for solutions to same/similar problems, and try to work it out on your own, but don’t spend an inordinate amount of time beating your head against the wall before asking for help)
Make it as easy as possible for people to provide a solution to your question. This means if possible, provide a clear explanation of your issue, your PBIX file, and if relevant a mockup of the results you want to see.
Be nice.
That’s it. You’ve done #1 and #3, and you’re working on #2 (don’t worry about making it perfect). There are tons of really talented folks here who are eager to help. Using the forum well will get you way up the learning curve rapidly, and before you know it you’ll be answering others’ questions.
If you’re interested, I put together the following video on practice strategies that will help you improve quickly, with the forum taking center stage in those strategies.
