Previous Year Dax not Working

[Anonymous]

Hello ,

I have a requirement to bring the amount of previous year i.e. previous YTD where the business is 'Renewal', I am using the formula:

RENPYTD = CALCULATE ( SUM ( PolBudget[Total Gross Premium] ), PolBudget[Policy.New Business Renewal]= "RENEWAL", PREVIOUSYEAR( PolBudget[Transaction Detail.Production Period],-12 ))

also used

RENPYTD = CALCULATE ( SUM ( PolBudget[Total Gross Premium] ), PolBudget[Policy.New Business Renewal]= "RENEWAL", PREVIOUSYEAR( PolBudget[Transaction Detail.Production Period].[Year],-12 ))

i think its not working because its not returning any value.

Please help.

[ToddChitt]

Let's assume you have the following in place:

1. A properly typed DATE column in your fact table (the table that contains the numbers)
2. A contiguous Date dimension (one row for every day, no gaps)
3. A relationship between the two on said Date column. (for this example, it will be 'Dates'[Date])

Start with your base measure:

My Measure = SUM('My Fact Table'[Amount])

Add a Year To Date measure for it:

My YTD = TOTALYTD ( [My Measure], 'Dates'[Date] )

Add a 'Last Year' version:

My Last Year = CALCULATE ( [My Measure], SAMEPERIODLASTYEAR ( 'Dates'[Date] )  )

Finally, add the Last Year To Date:

My LYTD = TOTALYTD ( [My Last Year], 'Dates'[Date] )

To verify, create a Table visual and add 'Dates'[Date] first, then each of the measures above.

To simplify your verification porcess, maybe only show it at the month granularity.

You should see Dates ONLY for cases where it can create the measure. So if you have Dates dimension members going back to 1/1/1900, but facts only going back to 1/1/2020, your table visual should start at 1/1/2020.

Assuming you plot by the months, the first 12 months will have values for [My Measure] and [My YTD] but not the other two because DAX can't make that calculation. Then for January of 2021, the value for [My Last Year] should be the same as [My Measure] for January 2020. 

Let us know how that goes.

[v-yalanwu-msft]

Hi, @Anonymous ;

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I am attaching the sample screenshot for review:

I have applied the filter on Year=2021 and New Business Renewal='RENEWAL'

The last column is RENPYTD which is blank where as the highlighted one is the desired result.

I am not able to attach a file as i dont have sufficient rights from this system.

[v-yalanwu-msft]

Hi, @Anonymous ;

Here "12" is not right, according to the offical document,

PREVIOUSYEAR(<dates>[,<year_end_date>])

I tested a simple example.

Measure = CALCULATE(SUM('Table'[value]),'Table'[type]=1,PREVIOUSYEAR('Table'[Date],"12/31"))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi ,

I have used the DAX provided by you:

RENPYTD = CALCULATE(SUM(PolBudget[Total Gross Premium]),PolBudget[Policy.New Business Renewal]="RENEWAL",PREVIOUSYEAR(PolBudget[Transaction Detail.Production Period],"12/31"))

its still returning the blank fields:

Please advise.

Thanks

[ToddChitt]

Try breaking your metric calculation into stages:

1. [Base Metric] = CALCULATE ( SUM ( PolBudget[Total Gross Premium] ), PolBudget[Policy.New Business Renewal]= "RENEWAL")
2. [Base Metric Last Year] = SAMEPERIODLASTYEAR ([Base Metric], 'Date'[Date])

Now plot the two side-by-side in a TABLE visual with Year and Month. You can tell if it's working by comparing numbers across years.

[Anonymous]

Hi @ToddChitt 

Getting error while creating last year function:

"SAMEPERIODLASTYEAR function expects a contiguous selection when the date column is not unique, has gaps or it contains time portion." and too this function can contain max 1 argument.

[ToddChitt]

You probably need a Dates (dimension) table joined to your measure table. 

My Dates Table = CALENDAR("1/1/2020", "12/31/2022")

Now go back to the PolBudget table, find the DateTime field, and convert its Data Type to DATE (or create a calculated column that returns a DATE type) join PolBudget to 'My Dates Table' on that column. Reference 'My Dates Table'[Date] in the SAMEPERIODSLASTYEAR function.