cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilC
Resolver I
Resolver I

Prior Year measure, with an additional field to consider

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.

 

 

vePub Year Example.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

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

2 REPLIES 2
amitchandak
Super User
Super User

@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.



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || Learn 50+ Power Query List Functions
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

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.

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.