Forum Discussion
Status Change
Hi dbattin4 ,
Thanks danextian , srlabhe and OktayPamuk80 for the detailed suggestions.
The approaches were on point, but the missing piece was that my dataset doesn’t always have entries on the exact month-end date. That’s why my original measure returned blanks.
As for my knowledge we can solve it by tweaking the logic to grab the latest record on or before the previous month end instead of looking for that single date.
Here’s the final measure that worked for me (sharing in case it helps others too).PreviousMonthState =
VAR _currentOpp = SELECTEDVALUE(Opportunities_ME[OpportunityNumber])
VAR _currentDate = MAX(Opportunities_ME[IngestionDate_EOM])
VAR _prevMonthEnd = EOMONTH(_currentDate, -1)
VAR _lastAvailableDate = CALCULATE( MAX(Opportunities_ME[IngestionDate_EOM]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) )
VAR _state = CALCULATE( MAX(Opportunities_ME[StateCode]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ) )
RETURN _state
Really appreciate all the guidance here.
Thanks,
Akhil.
Hi,
I think there is a step you need to do before you can achieve your need. Instead of using the date field from your Opportunities_ME table (and year and month for example), you need to create a separate calendar and connect the 2 tables over the date field. By this, you can select one month see data and still can have different measures showing you previous month or year.
You can create a calendar in the table view in Power BI Desktop using the "New Table":
You then type:
Calendar = calendar (min(Opportunities_ME[Date]),max(Opportunities_ME[Date]))
You get a table called Calendar including a Date field from a range Date in your Opportunities_ME table. You can create additional year, Yearmonth, month, etc. fields to be able to filter or use it in charts.
Once you connect the two tables in the model view, you can create the new measure:
PreviousMonthState = calculate(Min(Opportunities_ME[StateCode]), previousmonth(Calendar[Date]))
Regards,
Oktay
If it helped you would appreciate for a KUDOS and mark the answer as solution.
I have a semantic model where I am unable to add a seperate calendar table. There is a global calendar available but can you do this without using the calendar? If not I will get a relationship created
