Forum Discussion
Helper IIIIncorrect data for selected Previous and current periods
Hello- I've created a measure that will bring back data in the most current period and previous period (when two dates are selected). Ex: JAN-2021 and NOV-2020. If there isn't project data in one of the periods it categorizes the data in the CQ (Current Quarter) instead of in the quarter it belongs in. I would like to use DATEADD, but can't because we have Board Quarterly forecast data and monthly actuals in the same file. I have a sample PBIX file, but wasn't sure how to provide a link as it's in our company's Onedrive account. Thanks for your help!
Here are my measures:
6 Replies
- amitchandak
Super User
Jdokken , In case you have a standard period/month you should use time intelligence,
Else create a separate period column, with one of the column is YYYYPP , the create a rank column on that
new column
Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)measures
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))Last Year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period]=max('Period'[Period]) && 'Period'[Year]=max('Period'[Year])-1))
for standard one
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA 
JdokkenWe have quarterly data and monthly data coming from two different sources. Sometimes we are comparing the Board forecasts against eachother and sometimes we are comparing the forecast to Actuals. So in January 2021 we have Board data and Actuals data, but in February 2021 we'd only have Actuals data. In my sample above I'm comparing the Nov-20 and Jan-21 board data so I need it to look at the specific dates selected. If I used DATEADD it would compare it against the Dec-2020 Actuals.
Quarterly Board data:
Nov 2020 Board
January 2021 Board
May 2021 Board
Monthly Actuals Data:
December 2020 Actuals
January 2021 Actuals
February 2021 Actuals
March 2021 Actuals
April 2021 Actuals
May 2021 Actuals
- Jdokken
I have a Date & Source Column but it won't let me create a ranking off it because it needs an aggregation like min/max/count/sum to get a single result. I'm not sure how the ranking would work? It would have to Distinguish between the BOARD and IFD (actuals) data before it could rank.
V-lianl-msftCommunity Support
Hi Jdokken ,
If the problem persists,could you share the sample pbix with dummy data?A clear scenario helps to solve the problem.
Please remove any sensitive data before uploading.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- JdokkenI created this formula and it worked, except now it's bringing back the Sum of each project Name when rolled up to the Operating Group Level.CY Sales Board PQ =Var CurrDate = CALCULATE(MAX('Board & IFD'[Date]),FILTER(ALLSELECTED('Board & IFD'),'Board & IFD'[Date]))VAR PrevDate = CALCULATE(MAX('Board & IFD'[Date]),FILTER(ALLSELECTED('Board & IFD'),'Board & IFD'[Date] < CurrDate),VALUES('Board & IFD'[Project Name] ))VAR PrevAmount = CALCULATE([CY Sales Board],FILTER(ALLSELECTED('Board & IFD'), 'Board & IFD'[Date] = PrevDate),Values('Board & IFD'[Project Name]))RETURNPrevAmount
