Forum Discussion
HELP WITH CALCULATION BY MONTH
I HAVE A DATA SET THAT SHOWS MONTH IN THE BELOW FORMAT:
202001, 202002, 202003, 202004, 202005, ETC.
FIRST BEING JAN, FEB, AND, AND SO ON.
I WANT TO TAKE 202001 MONTHLY VOLUME AND DIVIDE IT BY 25 THEN MULTIPLY BY 24 AND THAT BE THE NEW VOLUME FOR 202002 OR JANUARY AND THEN KEEP THE REMAINING MONTHS AS THE VOLUME IS IN THE SYSTEM.
I AM USING A MEASURE CALLED (PY+VOLUME) - THIS IS THE MEASUARE I WANT TO CHANGE THE VALUE FOR JANUARY ONLY AND KEEP THE REMAINING MONTHS THE SAME.
I WAS ABLE TO USE CALCULATE TO GET THE VALUE BUT THEN IT DOESN'T POPULATE THE REAMINING MONTHS.
THANK AHEAD OF TIME FOR ANY HELP!! 🙂
I figured out a way to do it creating multiple calcs as the way you wrote it did not work for me as it would not make the column value available for an "if statement" -
since the month format of 202001, etc is in the form of a number or value in the table, I created a measure averaage(month) and then I used that measure in the if statement like this:
if(table(averagemonth)=202001,DIVIDE(TABLE(VOLUME),25)*24,
IF(TABLE(AVERAGEMONTH<>202001,TABLE(VOLUME)))
THIS WORKED FOR ME - NOT SURE WHY I COULDN'T GET YOUR SOLUTION TO WORK - BUT THANK YOU!!
4 Replies
CORRECTION WANT THE NEW VOLUME TO BE THE VOLUME FOR JANUARY OR 202001 BUT MAKE NO CHANGES TO THE OTHER MONTHS AND JUST POPULATE THE MEASURE (PY PLUS VOLUME).
hstgeorge you should add a new column like this
New Volume = IF( Table[Month] = '202001', DIVIDE ( Table[Volume], 25 ) * 24, Table[Volume] ) or for all Jan IF ( RIGHT ( Table[Month], 2 ) = "01",DIVIDE ( Table[Volume], 25 ) * 24, Table[Volume] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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 figured out a way to do it creating multiple calcs as the way you wrote it did not work for me as it would not make the column value available for an "if statement" -
since the month format of 202001, etc is in the form of a number or value in the table, I created a measure averaage(month) and then I used that measure in the if statement like this:
if(table(averagemonth)=202001,DIVIDE(TABLE(VOLUME),25)*24,
IF(TABLE(AVERAGEMONTH<>202001,TABLE(VOLUME)))
THIS WORKED FOR ME - NOT SURE WHY I COULDN'T GET YOUR SOLUTION TO WORK - BUT THANK YOU!!
hstgeorge I think I assumed that month column is a text but yes, if you remove quotes, it should work, pretty much the same idea what you did.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡
