Forum Discussion
Previous Month only Blank
The reason I asked because in your sample data one of the numbers (43) has the most recent data up to Oct, the rest is up to Nov and that was the reason for this question. Anyhow here is the measure for the previous month:
Previous month Count = VAR __currentDate = EOMONTH ( MAX ( Sheet1[date] ), -1 ) RETURN CALCULATE ( COUNTROWS ( Sheet1 ), DATESBETWEEN ( Sheet1[date], EOMONTH ( __currentDate, -1 ) + 1, __currentDate ) )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
28 Replies
- parry2k
Super User
Anonymous if you are working with dates, the best approach is to add a date dimension in your model, read my post here and then change your measure to use date from this new date dimension.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
Anonymous did you followed what I suggested and that is the key to your solution. Everything will work as expected once you have date dimension in your model.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
I've tried your suggestion but run into issues:
Error Message:
MdxScript(Model) (4, 88) Calculation error in measure 'CLIENT DATASET'[Previous month Count]: A column specified in the call to function 'PREVIOUSMONTH' is not of type DATE. This is not supported.The below measure refers to the new table
Previous month Count = CALCULATE( COUNTROWS('CLIENT DATASET') , PREVIOUSMONTH('Date'[Month]))
- parry2k
Anonymous in your function use PREVIOUSMONTH('Date'[Date] ) not PREVIOUSMONTH('Date'[Month] )
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
Anonymous It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.
Most of the data is sensitive so im unable to do that, sorry.
Previous month Count = CALCULATE( COUNTROWS('CLIENT DATASET') , PREVIOUSMONTH('Date'[Date]))This is the code i used to calculate how many of each different variable occured in the previous month,except its returning me data for the amount of times each variable occured throughout the set calandar
- parry2k
Anonymous this thing is super simple, I cannot make out anything without looking at it. Maybe create a dummy pbix file and share. if you cannot spend time doing that, not sure how I can assist.
- parry2k
Anonymous you can share it with everyone or you can share directly with me, my email address is in the footer.
- parry2k
Anonymous received the link, I will take a look.
- parry2k
Anonymous is this what you are looking for?
- parry2k
Anonymous can you put something in excel how you want to see the data, it is critical to understand to provide the solution, when you say a previous month, you can want to see the month or dates on the rows and then compare the count of that period with the previous month, no?
This is such a small piece of thing but because of not clear explanation, it is taking way too long, or maybe I'm not getting it.
Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490If you take the above example , imagine the values under the observations column are values that may occur each month a number of times (random). If the current month is october 2020, I want to compare the count for each value for this month to the previous month, with the ultimate goal of creating a measure of relative change in order of magnitude.
- parry2k
Anonymous so you will select a month to compare, correct? if there is no month selected, should it return blank() or use the current month. You have to think thru this and explain all of it. I don't like half baked solutions.
I have created 2 measures:
Current Month - provides total count for each value in a column for the current month
previous month - provides total count for each value in a column for the directly preceeding month
Difference: Current month - previous month
Currently the [previous month] measure is just returning the exact same results as if I want to count from the beginning to the end period. That shouldnt be what it does.