Create a measure to apply as a filter

[nh27]

Hi folks,

I have a PowerBI Report with about 10 pages and for about 4/6 of the visuals of each of these pages we have to keep these updated to show our Fiscal YTD position.

At the moment I am referencing a Month column in our data table and apply visual filters on each of these visuals.

Is there a way I can create a measure that I can use as a single reference point to update months? e.g. currently months 1-8 are selected based on a FY Apr-March but if there was was a way I can have a single measure that selects months 1-8 or 1-9 for next month that would then mass apply this change to all the visuals that would save alot of time.

Hope it makes sense what I am asking, any suggestions welcome.

Thank you in advance

[TomasAndersson]

Hi! 
Have you tried using DATESYTD() on your measures? DATESYTD function (DAX) - DAX | Microsoft Learn
With that you can get YTD without having to select several months.

Another possible solution would be to create a Date slicer and "Edit interactions" so that it only affects your YTD visuals. That should be able to get you a similar result.

Hope this helps!

[nh27]

Hi - thanks for the suggestion.

The problem I have is I need to create the measure first, but I'm baffled on how to go about it.

If I can get a measure to just select Months 1-8 from the Months Column I could just apply that measure to the visual, then when it comes to the next month I just update that one measure to include months 1-9 etc.

The YTD and MTD measures would need variation as the Fiscal Year I am working with is Apr-Mar but the data table already has the months 1-12 adjusted for apr-mar.

Thanks

[TomasAndersson]

Sorry, it might just be me who doesn't understand how your data is structured, but I'm still thinking DATESYTD should be a possible solution, if you have a date table. You can define what your FY end date is there. Maybe you ca

YTDMeasure = 
CALCULATE(
    [Measure],
    DATESYTD(Dates[Date], "3/31")  //Dates[Date] or whatever you date column is
)

 If not this approach could also work. 

YTDMeasure2 = 
VAR __MonthNumber = 9  //Needs to manually updated every month. This could be its own measure that you can reference in several other measures.

VAR __StartDate = STARTOFYEAR(Dates[Date],"3/31")  //Dates[Date] refer to a Date column
VAR __EndDate = Date(Year(max(Dates[Date]),__MonthNumber+1,1) - 1 //+1 Mo and - 1 Day to get to last day of MonthNumber regardless of number of days in the month
return
CALCULATE(
    [Measure],
    DATEBETWEEN(Dates[Date], __StartDate, __EndDate
)

 Instead of creating MonthNumber as a measure, you could also create a new slicer table with values 1-12 and use that to define what month it is.

If I'm still way off, maybe you can share some data and the measure you want to have YTD so I can understand better.

[nh27]

Hi,

Apologies its taken some time to respond but I have attached some info regarding the tables/relationships if it helps.

Our Sales Data is not in a full date format but by period effectively i.e. 1-12

We have created a period lookup table to define our Fiscal Year effectively, i.e. Period 1 = April and also added a Month Name Short so we can use the month name as a slicer selection

In the visuals itself, we are updating the Month to effectively make it YTD.

I guess my question is is there a Measure I can create that will select Months 1 - 9 from one measure itself that I can update myself next month e.g. 1-10 and then all of my 12+ visuals can just reference this measure as a filter as opposed to manually updating each visual?

Hope this makes more sense



Many thanks

[nh27]

Hi all, wondering if anyone has any ideas on my previous comment?

Thanks in advance