Hi, what would be the measure that I need to use if I have a visual which needs to show the 12 month roll of YTD and start of the year entity and another visual say a Card visual which shows the normal output of data per month?
Hi Pat, Firstly, the way that I would do this is use the Dates table to drive the x-axis of both visuals and separate slicers to capture th euser intent. The Slicers that I use in the The slicers that I use in the video are: DatesSlicers[FiscalYear] = 2022 DatesSlicers[Month] = Jun AnalysisPeriod[Period] = YTD Then I would set up a pair of Dates measures [MaxDate] and [Mindate] that reads these slicers and return the following values: 1. If there is no Date filter on the Measures then in this case they will return [MaxDate] = 30th June 2021 and [MinDate] = 1st April 2021. 2. If there is a Date filter on these Measures then: a. If the MAX or MIN Date value is outside the range selected via the sclicers, then the measures return Blank(). b. Otherwise they return MAX ( Dates[Date] ) and MIN ( Dates[Date] ) respectively. 3. For both visuals that you ask about, use Dates as the x-axis. 4. To adjust the x-axis dynamially I use would this code (or something similar) in both the rolling 12 month and "regular" measure: Var MaxDate = [MaxDate] Var MinDate = [MinDate] Return IF ( OR ( ISBLANK ( MaxDate ), ISBLANK ( MinDate ), Blank() ), < rolling 12 month calculation > or < regular calculation > ) 5. Then in each measure we manipluate the Filter Context on Dates to get what we want. 6. Regular "Sales Amount" calculation might look like this: CALCULATE ( SUM ( Sales[Amount] ), ALL ( Dates ), Dates[Date] = MinDate ) 7. For Rolling 12 Months (assuming in this case that the Dates axis is Month) I capture MaxRelativeMonth that relates to MaxDate: e.g. Var MaxDate = [MaxDate] Var MaxRelativeMonth = CALCULATE ( MAX ( Dates[RelativeMonth] ), ALL ( Dates ), Dates[Date] = MaxDate ) Return CALCULATE ( SUM ( Sales[Amount] ), ALL ( Dates ), Dates[RelativeMonth] MaxRelativeMonth ) Note: This explains in principle how I would tackle the problem. Actually, because of the way I do this, it is a bit more efficient to use measures [MaxRelativeDay] and [MinRelativeDay] where Today() has a Dates[RelativeDay] value = 0, Yesterday is -1. I also (as implied above) have a Dates[RelativeMonth] column. I hope this helps. Regards, Hugh
What is the purpose of buying this template? If I wanted to use this for the company I work for wouldn't I need a data set that matches the one you are using? I'm interested in buying but how can I actually use this?
Hi BrotherCole. Thanks for your question. It would of course need adapting to your own model. You are correct. The model underneath this is quite simple and I will be posting videos with more details. Regards, Hugh
Hi, what would be the measure that I need to use if I have a visual which needs to show the 12 month roll of YTD and start of the year entity and another visual say a Card visual which shows the normal output of data per month?
Hi Pat,
Firstly, the way that I would do this is use the Dates table to drive the x-axis of both visuals and separate slicers to capture th euser intent. The Slicers that I use in the
The slicers that I use in the video are:
DatesSlicers[FiscalYear] = 2022
DatesSlicers[Month] = Jun
AnalysisPeriod[Period] = YTD
Then I would set up a pair of Dates measures [MaxDate] and [Mindate] that reads these slicers and return the following values:
1. If there is no Date filter on the Measures then in this case they will return [MaxDate] = 30th June 2021 and [MinDate] = 1st April 2021.
2. If there is a Date filter on these Measures then:
a. If the MAX or MIN Date value is outside the range selected via the sclicers, then the measures return Blank().
b. Otherwise they return MAX ( Dates[Date] ) and MIN ( Dates[Date] ) respectively.
3. For both visuals that you ask about, use Dates as the x-axis.
4. To adjust the x-axis dynamially I use would this code (or something similar) in both the rolling 12 month and "regular" measure:
Var MaxDate = [MaxDate]
Var MinDate = [MinDate]
Return
IF (
OR (
ISBLANK ( MaxDate ),
ISBLANK ( MinDate ),
Blank()
),
< rolling 12 month calculation > or < regular calculation >
)
5. Then in each measure we manipluate the Filter Context on Dates to get what we want.
6. Regular "Sales Amount" calculation might look like this:
CALCULATE (
SUM ( Sales[Amount] ),
ALL ( Dates ),
Dates[Date] = MinDate
)
7. For Rolling 12 Months (assuming in this case that the Dates axis is Month) I capture MaxRelativeMonth that relates to MaxDate: e.g.
Var MaxDate = [MaxDate]
Var MaxRelativeMonth =
CALCULATE (
MAX ( Dates[RelativeMonth] ),
ALL ( Dates ),
Dates[Date] = MaxDate
)
Return
CALCULATE (
SUM ( Sales[Amount] ),
ALL ( Dates ),
Dates[RelativeMonth] MaxRelativeMonth
)
Note: This explains in principle how I would tackle the problem. Actually, because of the way I do this, it is a bit more efficient to use measures
[MaxRelativeDay] and [MinRelativeDay] where Today() has a Dates[RelativeDay] value = 0, Yesterday is -1. I also (as implied above) have a Dates[RelativeMonth] column.
I hope this helps. Regards, Hugh
What is the purpose of buying this template? If I wanted to use this for the company I work for wouldn't I need a data set that matches the one you are using? I'm interested in buying but how can I actually use this?
Hi BrotherCole. Thanks for your question. It would of course need adapting to your own model. You are correct. The model underneath this is quite simple and I will be posting videos with more details. Regards, Hugh
I have posted this video, that might help to answer your question. ua-cam.com/video/r0wx5ucHLb0/v-deo.html