Prior Year measure, with an additional field to consider

[PhilC]

I am looking to see if there is a more efficient approach for the measure below, and also want another measure to calculate a Prior Year (PY) version. (to be used in YoY variance).

The data represents publications, which have an attribute called PUB_YEAR. This is a single year value. There is also a REPORTABLE_DATE_ADJUSTED field which represents the date the record was captured in the system and had a RELATIONSHIP with the Dates table. The REPORTABLE_DATE_ADJUSTED can occur well after the PUB_YEAR, ie in subsequent years, so these need to be ignored in the YTD counts.

The measure below is designed to represent the YTD position for any given PUB_YEAR, such that as different months are selected from the dates table, the YTD counts update, and if a month after the current month is selected, then earlier years will increase but the current year will not.

# Reportable Publications YTD = 
VAR PubYearLast = MAX(Dates[Year])
VAR PubYearFirst = PubYearLast - 3

VAR Result = 
CALCULATE(DISTINCTCOUNT(PUBLICATIONS[PUBLICATION_ID]),
    ALL(Dates),
    FILTER(PUBLICATIONS, 
            PUBLICATIONS[PUB_YEAR] = YEAR(PUBLICATIONS[REPORTABLE_DATE_ADJUSTED]) &&
            MONTH(PUBLICATIONS[REPORTABLE_DATE_ADJUSTED]) <= MONTH(MAX(Dates[Date]))
    ),
    USERELATIONSHIP(PUBLICATIONS[REPORTABLE_DATE_ADJUSTED],Dates[Date])
)

Return Result

I cannot work out the logic for a # Reportable Publications YTD PRIOR YEAR measure.

It needs to present the value from the prior PUB YEAR (ie if PUB_YEAR is 2022, it should show the count for the YTD for the 2021 PUB YEAR. ie excluding any activity not only happening after the reporting month from the previous year, but also any activity in months up to the reporting month in subsequent years. So, I do not think functions like DATAADD and SAMEPERIODLASTYEAR will assist due to having to move back one year with PUB_YEAR as well as with the REPORTABLE_DATE_ADJUSTED.

See screenshot for example.

ve

Will work on mocking up a detailed dataset to help, but posting now in case someone can help based on the measure displayed above

[amitchandak]

@PhilC , First of Date table should be used in filter, slicer and visual for all period related stuff

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Use relationship example

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),DATESYTD('Date'[Date]))

YTD another option

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Use a userelationship measure in place of net

prefer to measure with userealtionship and then use it with TI

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[PhilC]

Hi Amit, thanks for providing the links & info etc.

Not sure I see which ones relate to the measure I am after. 

For clarity:

- A Date table exists

- A filter will be on the visual for a Month-Year (this is dynamic, they user can select from mutiple years and all months

- The Relationship is there and active between the Date table and Publications (data) table.

It is addressing the complexity around having to include another field in the DAX that is offset by 1 year.

Here is what I have:

# Reportable Publications YTD PY = 
VAR PubYearPY = MAX(PUBLICATIONS[PUB_YEAR]) - 1
VAR CurrentMonth = MONTH(MAX(Dates[Date]))

VAR Result = 
CALCULATE(DISTINCTCOUNT(PUBLICATIONS[PUBLICATION_ID]),
    ALL(Dates),
    ALL(PUBLICATIONS[PUB_YEAR]),
    FILTER(PUBLICATIONS,  
        PUBLICATIONS[PUB_YEAR] = PubYearPY &&
        (PUBLICATIONS[PUB_YEAR] = YEAR(PUBLICATIONS[REPORTABLE_DATE_ADJUSTED]) &&
        MONTH(PUBLICATIONS[REPORTABLE_DATE_ADJUSTED]) <= CurrentMonth )
        ),
    USERELATIONSHIP(PUBLICATIONS[REPORTABLE_DATE_ADJUSTED],Dates[Date])
)

--Return PubYearPY
--Return CurrentMonth
Return Result

PubYearPY returns the correct (prior) PUB_YEAR

CurrentMonth returns the correct month # based on the filder selection on the page

However the Result is blank.