Divide this month data with last month data

[rezaaditia]

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

[Anonymous · Accepted solution]

@rezaaditia 

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: 

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/ESTzvGfxWBZPgUxU7B1M...

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.

[rezaaditia]

Hi -

Thank you for your solution and sorry for late response, was on travel.

btw i am not able to open your file,

Thanks 

Reza

[Anonymous]

@rezaaditia 
Try:
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/ESTzvGfxWBZPgUxU7B1M... 

Basically 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

[rezaaditia]

Hi @Anonymous 

Thank you, i think i can't open due the version on mine still June 2019. I already open ticket to IT to update my power bi.

Thanks a lot for your feedback, really appreciate it.

[amitchandak]

@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-Y...


Appreciate your Kudos.