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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DouweMeer
Impactful Individual
Impactful Individual

Previousperiod not working - clarification request on context

I feel like a beginner for asking this, but I'm not getting previousperiod (year/ quarter/ month) working. 

So I get 2 tables (technically I have 15, but not in context) that are connected by date by a single direction on date. 

DouweMeer_0-1683046234038.png

In a manner of speaking, it is the sum of qty by date. So I wrote something like:

calculate ( sumx ( 'Shipments' , 'Shipments'[Qty] ) , previousyear ( 'Date table'[Date] ) )

But this returns just but blank values. To get this working, by what I come up with as solution, is this:

VAR a1 = year ( max ( 'Date table'[Date] ) )

calculate (
   sumx ( 'Shpments' , 'Shipments'[Qty]
   , year ( 'Date table'[Date] ) = a1 -1
   )

This works, but I don't want to get in the hassle of doing this by quarter as 1st quarter return in Q0, which doesn't exist. Therefore I have to state:

VAR a1 = quarter ( max ( 'Date table'[Date] ) )
VAR a2 = if ( a1 = 1 , -1 , 0 ) + year ( max ( 'Date table'[Date] ) )
VAR a3 = if ( a1 = 1 , 4 , a1 )
RETURN
calculate ( 
   sumx ( 'Shipments' , 'Shipments'[Qty] )
   , year ( 'Date table'[Date] ) = a2 && quarter ( 'Date table'[Date] ) = a3 
   )

 O, I hate this. Now it suddenly does work with previousquarter. 

Do I break the functionality if I don't have the relationship like above, but like this? Must be. But what does it do different? How could I still make it work if I would have done it through that?

DouweMeer_1-1683047160941.png

Code is not like some woman who changes opinion based on whether she's fed or not. 

(reference for those who didn't understand)

DouweMeer_4-1683047272293.png

Who knows how relationships cause the previousperiod to be working?

2 REPLIES 2
amitchandak
Super User
Super User

@DouweMeer , this code will work at year level, below year level it will full last year data, if date table is marked as date table, has full date, Join is single directional and join column in Shipment do not have timestamp

 

calculate ( sum ( 'Shipments'[Qty] ) , previousyear ( 'Date table'[Date] ) )

 

year behind measure

 

calculate ( sum ( 'Shipments'[Qty] ) , dateadd( 'Date table'[Date],-1, year ) )

 

month behind

calculate ( sum ( 'Shipments'[Qty] ) , dateadd( 'Date table'[Date],-1, month) )

 

 

more example

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

 

 

You can also cosider offset and also consider how I used switch if needed

Offset Previous Month = CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year]),ORDERBY('Date'[Month Year sort]))) 

Previous Month in Year = CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]),KEEP,PARTITIONBY('Date'[Year]))) 

https://medium.com/microsoft-power-bi/power-bi-offset-function-84177cc2cafd

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

I think I found out why I am struggling. If I do:

calculate ( max ( 'Date table'[Date] ) , previousquarter ( 'Date table'[Date] ) )

It does what you expect it to do... except one:

DouweMeer_0-1683106998933.png

The total-total is blank. Sure, makes sense. The behavior is that it takes the previous quarter on the minimum date within context. Therefore, also Q1's column total is blank because, in my model, Q4 2020 does not exist. 

What I want, what would be useful, is if the total total shows the previous quarter from today's date. BUT, it should not show previous quarter from today on the category of Q1's column total. 

So I come up with this:

VAR a1 = calculate ( min ( 'Custom - Date Table'[Date] ) , PREVIOUSQUARTER( 'Custom - Date Table'[Date] ) )
RETURN
SWITCH( TRUE()
     , isfiltered ( 'Custom - Date Table' ) , a1
     , calculate ( a1 , 'Custom - Date Table'[Date] = TODAY() ) 
     )

Which does not return what I want...

DouweMeer_1-1683108445786.png

The funny part is this:

VAR a1 = calculate ( min ( 'Custom - Date Table'[Date] ) , PREVIOUSQUARTER( 'Custom - Date Table'[Date] ) )
RETURN
SWITCH( TRUE()
     , isfiltered ( 'Custom - Date Table' ) , "Is filtered"
     , /* calculate ( a1 , 'Custom - Date Table'[Date] = TODAY() ) */ "Alternative"
     )

Which returns this:

DouweMeer_2-1683108651453.png

A, why are all my returned values "alternative" and B, why do they still show the correct value? Shouldn't this part:

calculate ( a1 , 'Custom - Date Table'[Date] = TODAY() )

Just return the same values?

frustrated-ugh

At this moment I'm just thinking of creating a new custom table with 1 record and a custom table with "today()" and then manually enter it. Why is the context of a visual so stupidly different than you'd expect? (rhetorical)

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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