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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.