Month on Month - error

[SamuraiEd]

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.

Charges MoM % =

VAR LM = CALCULATE(SUM(Electricity[Charges]), PREVIOUSMONTH('Calendar'[Date]))

VAR CM = SUM(Electricity[Charges])

VAR DelataM = CALCULATE(CM - LM)

RETURN

IF(NOT ISBLANK(CM), DIVIDE(DelataM, LM))

Using DATEADD and PARALLELPERIOD gives the same results.

I also tried to use this VAR Lastactualdate = LASTNONBLANK('Calendar'[Date],SUM(Electricity[Charges])) and  use it with the parallelperiod but that didnt help.

Not sure if you can see where im going wrong.

[SamuraiEd]

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)

Return

Result

Measure 2

TotalWaterConsumptionMOM 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 - LM

Return

IF(NOT ISBLANK(CM), DIVIDE(DeltaM,LM))

FYI My calendar table ranges from 1/1/2017 to 31/12/2020

[SamuraiEd]

Im 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.

[amaleranda]

@SamuraiEd ,

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()) - 1

VAR YearMnth = CONCATENATE(CurrentYear,PreviousMnth)

VAR Result =

CALCULATE( [Current Total], 'Date'[Year Month] = YearMnth)

RETURN

Result

[SamuraiEd]

Im not sure if the translation worked correctly but it didnt seem to help.

I am thinking now that it would be easier to create a clicer and disabling the interactions with other tiles....

I do think there must be a solution to what im looking for though.!

[amaleranda]

@SamuraiEd ,

challange here is to work out what did you want to do when user make a selction, when a user select a different month from a then card visual returns blank as there would either no previous month total or current month total. My understanding was you need to see the % comparing previous month to current month. There's no visual level filter on the card.  

 

[SamuraiEd]

Thank you. Yes I am looking at a one card like you have showing MOM%.

Previous Month

PreviousMonthTotal =

Var CurrentTotal = 'Data Water'[Total water consumption]

Var CurrentYear = YEAR(TODAY())

Var PMonth = MONTH(TODAY())-1

VAR Yearmonth = CONCATENATE(CurrentYear,PMonth)

Var Result = CALCULATE(CurrentTotal,'Calendar'[Year Month]=Yearmonth)

Return

Result

Current Month

CurrentMonthTotal =

Var CurrentTotal = [Total water consumption]

Var YearMonth = FORMAT(TODAY(),"YYYY-MM")

Var Result = CALCULATE(CurrentTotal,'Calendar'[YYYY-MM]=YearMonth)

Return

Result

My Calendar looks like this:

My Current Month and Previous Month results appear to be the same too.

Really not sure if i have missed out one of your instructions.

Ill give this one more go, then probaby have to resort to a slicer. 

[v-xuding-msft · Accepted solution]

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?

[amaleranda]

@SamuraiEd ,

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] )

RETURN

Result

CurrentMonthTotal =

VAR CurrentTotal = [Current Total]

VAR YearMonth = FORMAT(TODAY(),"YYYY-MM")

VAR Result = CALCULATE([Current Total], 'Date'[Year Month] = YearMonth)

RETURN

Result

hope this helps, if it works for you please acccpet this as a solution.

info@nykensanalytics.com.au 

[amitchandak]

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

1. You do not have all dates in calendar table.
2. 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.
3. 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]))

[Greg_Deckler]

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

[SamuraiEd]

Thanks Greg. A first post rookie! will improve for the next post!