Forum Discussion
Helper IIIDivide this month data with last month data
Hi All -
Need your assintance with my issue. I have 2 tables, one is using OLAP and another is OLTP.
In OLAP, i have monthly data which is showing EOM Outstanding data.
Now the issue, i want to know the percentage of my OS of total records of last month.
Please advise how to divide my EOM OS / Total records of last month.
Sample :
on 31 Dec 19 my OS = 3,852 and Total Records on 2019-Nov 13,908
the percentage will be 3,852/13,908 = 27.7%
I have upload my sample on dropbox
https://www.dropbox.com/s/07ca6wm54tiytty/sample.pbix?dl=0
Thank you in advance
Reza
You would need to create another Calendar table in order to relate the 2 tables.
Calendar Table = CALENDARAUTO()The key is the you need to the get the ID count of previous month:
Total Prev month= CALCULATE(COUNT(OLTP[ID]),PREVIOUSMONTH('Calendar Table'[Date]))Check the measures and relationships with my test file:
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
rezaaditia , you can use time intelligence with date table for that
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month)) Variance = divide([MTD Sales] -[Last MTD Sales],[last MTD Sales])To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos. You would need to create another Calendar table in order to relate the 2 tables.
Calendar Table = CALENDARAUTO()The key is the you need to the get the ID count of previous month:
Total Prev month= CALCULATE(COUNT(OLTP[ID]),PREVIOUSMONTH('Calendar Table'[Date]))Check the measures and relationships with my test file:
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rezaaditiaHi -
Thank you for your solution and sorry for late response, was on travel.
btw i am not able to open your file,
Thanks
Reza
rezaaditia
Try:
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/ESTzvGfxWBZPgUxU7B1MHJABThww631F3nv20JB_ajMPAQ?e=P7uutgBasically other I used Date from the CalendarTable, created the Total of previous month.
Total Prev month= CALCULATE(COUNT(OLTP[ID]),PREVIOUSMONTH('Calendar Table'[Date]))Then calcuate the percent with:
% = SUM('OS-OLAP'[OS])/[Total]And finally put 'OS-OLAP'[OS], Total measure and % measure in the table.
Regards
Paul
