Forum Discussion
Previous Period Calculation and Composite Model
Context:
1. I'm using the feature released earlier this year and allowing us to create a composite dataset based on several published datasets. To join the conformed dimensions (like calendar), I'm importing them into my composite model and linking all identical dimensions togethere with a 1:1 relationship (For instance, each of my 3 child models have a dim_calendar table, I'm importing the dim_calendar table in the composite model and link the 3 child dim_calendar tables on the unique calendar_key
2. All measures comping from the child models except one (see below) are working perfectly in the composite model.
2. In one of my data model, I calculating a "Previous Period" measure (I can't used PREVIOUSMONTH() function as a period is defined here as a group of 4 or 5 full weeks):
# Delivered Packages Previous Period =
VAR _CurrentPeriod =
SELECTEDVALUE( 'Delivery Calendar'[OPS Period Index] )
VAR _PreviousPeriod =
CALCULATE( MAX( 'Delivery Calendar'[OPS Period Index] ),
FILTER(
ALLSELECTED( 'Delivery Calendar' ),
'Delivery Calendar'[OPS Period Index] < _CurrentPeriod ) )
VAR _Result =
CALCULATE( [# Delivered Packages],
FILTER(
ALLSELECTED( 'Delivery Calendar' ),
'Delivery Calendar'[OPS Period Index] = _PreviousPeriod
)
)
RETURN
_Result
In the model where this measure has been created, it works fine. However, whne I want to use it in the composite model, it's not working, see the 2 behaviours in the screenshot below:
I really want to understand what is not working as we are planning to base our data access solutions on composite datasets.
2 Replies
- amitchandak
Super User
korrigan , Based on what I got so far
In you date table create a column rank on year period
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
Then you can have measure like example measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))Same what we do for week
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8- korriganRegular Visitor
Thank you for your answer but I'm not sure it's solving my problem: The column "OPS Period Index" I'm using is the same as the PEriod Rank you're proposing (the only difference is that it is calculated in the database).
The problem is the fact that my formula (or yours) is not working when I bring it in a composite dataset (aka: a dataset based ona published dataset).