Forum Discussion
Month on Month - error
Hi, I am sure this is an easy one for the pros. I am struggling though.
I am trying to do what should be a simple MoM % but it keeps showing the sum of the MoM% as the final figure when I put the metric on a one card.
It only seems to work if I add a filter separately to the tile for the particular month/year. meaning month by month the formula gives the correct result but not if its unfiltered. This isnt really workable as I would have a lot of tiles to change each month.
This is the DAX I am using.
Hi SamuraiEd ,
I find that you have tried the formula below:
TotalWaterConsumptionMOM Test 1 = Var CM = [CurrentMonthTotal] Var LM = CALCULATE('Data Water'[Total water consumption], PREVIOUSMONTH('Calendar'[Date])) Var DeltaM = CM - LM Return IF(NOT ISBLANK(CM), DIVIDE(DeltaM,LM))You could create a new measure to summarize it to get the total value.
Measure = SUMX('Data Water',[TotalWaterConsumptionMOM Test 1])Is the result what you want?
11 Replies
SamuraiEd - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.All of that said, you may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.I'm not 100% clear on what is wrong. Screen shots, sample data, etc. would help.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Thanks Greg. A first post rookie! will improve for the next post!
SamuraiEd , Ther can few reasons it is not working. Calendar table is not marked as date table . Option when right-click on the table you will have option.
- You do not have all dates in calendar table.
- You may have a timestamp in your date and it is not visible because of the datatype date or the format that does not include time. Change the datatype to datetime and have a format that shows time to check.
- In above case, if you have date with timestamp. Create a new date column and join it with date calendar.
Sales Date 1= [Sales Date].Date
Sales Date 2= Date(Year([Sales Date]), Month([Sales Date]), Day([Sales Date]))Create a Variabel to get the current month as below and then put that in the CM measure to filter out only the current year month. make sure you have a column in you calader tabel called year month to the format of "YYYY-MM". Then use below variable in the CM measure to only filter out dates from the calendar tabel. it should give you only the current month value.
Your current CM measuer does not filter out any dates hence it returns sum of charges to the card. if you use a table of matrix with month on row it should work, but in a card visual theres not filter context coming to the aggragrates unless you use visual level filters.
Current Total =VAR Result =SUM ( Sheet1[Total] )RETURNResultCurrentMonthTotal =VAR CurrentTotal = [Current Total]VAR YearMonth = FORMAT(TODAY(),"YYYY-MM")VAR Result = CALCULATE([Current Total], 'Date'[Year Month] = YearMonth)RETURNResulthope this helps, if it works for you please acccpet this as a solution.Hi all,,
sorry for the delay. amaleranda I put in your formula but its the same result as my previous others. See below. The boxes with filter for this month year is highlighted in blue but im looking to achieve that without filters (white box).
Measure 1
CurrentMonthTotal =Var CurrentTotal = [Total water consumption]Var YearMonth = FORMAT(TODAY(),"YYYY-MM")Var Result = CALCULATE(CurrentTotal,'Calendar'[YYYY-MM]=YearMonth)ReturnResultMeasure 2TotalWaterConsumptionMOM Test 1 =Var CM = [CurrentMonthTotal] ((note: I dont think it makes a difference having the currentmonthtotal. Total water consumption looks ok))Var LM = CALCULATE('Data Water'[Total water consumption], PREVIOUSMONTH('Calendar'[Date]))Var DeltaM = CM - LMReturnIF(NOT ISBLANK(CM), DIVIDE(DeltaM,LM))FYI My calendar table ranges from 1/1/2017 to 31/12/2020Im sorry I am not sure if this is the way to attach source data.
MonthDomesticConsumptionNeWaterConsumption
1-Jan-19 1,305.60 2,538.20 1-Feb-19 1,963.90 2,567.30 1-Mar-19 2,220.20 2,788.40 1-Apr-19 1,989.40 2,546.20 1-May-19 2,203.60 2,514.30 1-Jun-19 2,248.20 2,936.70 1-Jul-19 2,546.10 2,132.80 1-Aug-19 2,630.80 2,592.90 1-Sep-19 2,717.30 2,922.30 1-Oct-19 2,782.00 2,757.60 1-Nov-19 865.30 2,234.50 1-Dec-19 1,912.90 2,662.40 1-Jan-20 2,863.40 2,035.00 1-Feb-20 2,867.50 2,424.40 1-Mar-20 2,107.40 1,308.10 1-Apr-20 1,200.80 1,896.80 1-May-20 529.00 1,958.20 1-Jun-20 600.00 1,774.60 1-Jul-20 710.00 1,800.30 1-Aug-20 1,231.90 2,043.20 I also think the screenshot might be blurred so here it is again.
Try using below measure for the Previous month. for this measure to work you also need [Year Month] column calculated in your calander tabel. from this measure you can workout % change over the last month. Not sure what are your slicer requirments. it turns blank since other filers on the page has an effect on the card. try turn off visual intractions with the card to the rest of the visuals on the page.
=======year month Column==========
"Year Month",FORMAT([Date],"YYYYM")=======Previous Month Total measure==========
PreviousMonthTotal =VAR CurrentTotal = SUM ( Sheet1[Total] )VAR CurrentYear = YEAR(TODAY())VAR PreviousMnth = MONTH(TODAY()) - 1VAR YearMnth = CONCATENATE(CurrentYear,PreviousMnth)VAR Result =CALCULATE( [Current Total], 'Date'[Year Month] = YearMnth)RETURNResult
