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
Danny2020
Regular Visitor

Time Intelligence last year measure - quarterly amounts

Hello,

 

I have a large table with quarterly amounts like this:

 

Desc 1Desc 2Desc 3Desc 4Desc 5Desc 6YearQuarterDateActual
AAABBBCCCDDDEEEFFF2012Q13/31/2012xxx
AAABBBCCCDDDEEEFFF2012Q26/30/2012xx

 

I calculated the date column in Power Query, to show the last date for each quarter. This date has a relationship with a calendar table.

 

I tried to create a measure in DAX to calculate the last year (2011) amount for each quarter. I have complete data for each of the  quarters in 2011 and with the same values from Desc 1 to Desc 6 (description). However, because I don't have daily data, the measure returns empty value. 

 

How to create a measure in DAX to show last year - last quarter for each line?

 

Thanks

2 REPLIES 2
Danny2020
Regular Visitor

Hi @amitchandak 

 

Thank you for your reply. I previously tried to use the time intelligence measures you mentioned but they are returning blank. I think the reason is that the data is available only for the end of each quarter, e.g. (3/31/2012), (6/30/20212), so it is not time continuous.

 

 

amitchandak
Super User
Super User

@Danny2020 , Join the date of your table with the date of a date table. It should be marked as a date table. You need to use the field from the date table in measure, visual and slicer.

 

You can try measure like

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Helpful resources

Announcements
Europe Fabric Conference

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors