Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Previous Year Dax not Working

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.
8 REPLIES 8
ToddChitt
Super User
Super User

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





v-yalanwu-msft
Community Support
Community Support

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
Not applicable

Hi,

I am attaching the sample screenshot for review:

Vaibhav_rathore_0-1661334326230.png

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
Community Support
Community Support

Hi, @Anonymous ;

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

 

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

 

AilsaTao_0-1661220850918.png

I tested a simple example.

 

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

 

The final show:

AilsaTao_1-1661221389091.png


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
Not applicable

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:
Vaibhav_rathore_1-1661330675374.png

Please advise.

Thanks


 

ToddChitt
Super User
Super User

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Anonymous
Not applicable

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.

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.