The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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?
Code is not like some woman who changes opinion based on whether she's fed or not.
(reference for those who didn't understand)
Who knows how relationships cause the previousperiod to be working?
@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
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:
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...
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:
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?
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)