Urgent Help ---- DOD, MONTH, YEAR

[Anonymous]

Hi,

Please see the below query. Can someone share some solution for this query.

Urgent needed.

[mahoneypat · Accepted solution]

You posted this in the Power Query area, but this is better solved with DAX.  You should (in query) unpivot your data so you have a Date column and a Value column (or whatever your #s are).  Then you make a relationship to a Date table and do measures that calculate each of your three measures with a pattern like this:

NewMeasure =
VAR currentdate =
MAX ( 'Date'[Date] ) // assumes you are using the Date column from your Date table in your visual
VAR currentvalue =
SUM ( Table[Value] ) // substitute with your table and column name. if only one row, doesn't matter if min max sum or average is used
VAR comparisonvalue =
CALCULATE ( SUM ( Table[Value] ), 'Date'[Date] = currentdate - 1 )
RETURN
comparisonvalue - currentvalue

The above gives you your DOD measure.  For the other two, substitute the below for the comparisonvalue line

var comparisonvalue = Calculate(SUM(Table[Value]), ENDOFMONTH('Date'[Date])) // var currentdate not needed

var comparisonvalue = Calculate(SUM(Table[Value]), ENDOFYEAR('Date'[Date])) // var currentdate not needed

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

mahoneypat's suggestion should be helpful.

If your real data have more complex problem, please feel free to ask us.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

mahoneypat's suggestion should be helpful.

If your real data have more complex problem, please feel free to ask us.

Best Regards

Maggie

[mahoneypat · Accepted solution]

You posted this in the Power Query area, but this is better solved with DAX.  You should (in query) unpivot your data so you have a Date column and a Value column (or whatever your #s are).  Then you make a relationship to a Date table and do measures that calculate each of your three measures with a pattern like this:

NewMeasure =
VAR currentdate =
MAX ( 'Date'[Date] ) // assumes you are using the Date column from your Date table in your visual
VAR currentvalue =
SUM ( Table[Value] ) // substitute with your table and column name. if only one row, doesn't matter if min max sum or average is used
VAR comparisonvalue =
CALCULATE ( SUM ( Table[Value] ), 'Date'[Date] = currentdate - 1 )
RETURN
comparisonvalue - currentvalue

The above gives you your DOD measure.  For the other two, substitute the below for the comparisonvalue line

var comparisonvalue = Calculate(SUM(Table[Value]), ENDOFMONTH('Date'[Date])) // var currentdate not needed

var comparisonvalue = Calculate(SUM(Table[Value]), ENDOFYEAR('Date'[Date])) // var currentdate not needed

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi, 

First of all many many thank to resolve my query.

I am facing one problem the result will come correctly (It adding sum on both value while I am using the measure.) I need to subtract the value. Please refer the screen shot for your reference. 

Kindly inform to me, where I am wrong.  Please suggest asap.

Thanks

[mahoneypat]

It looks like you've made calculated columns.  I was thinking you would unpivot the first 4 columns in your original pic/table, and then create measures with the expressions I sent, and create those columns in a table visual.  If you do not need columns, it is better to use measures.  Can you clarify what your model looks like now, and describe the final visual you'd like to have on your report?

Regards,

Pat

[Anonymous · Accepted solution]

Hi Pat,

Thanks for your help. 

The Code is : 

DOD = VAR CurrentDate = MAX(DataTbl[Date])

VAR CurrentValue = CALCULATE(SUM(DataTbl[Balance]),DataTbl[Date] = CurrentDate )

var ComparisonDate = calculate(MAX(DataTbl[Date]),filter(DataTbl,DataTbl[Date]<CurrentDate))

VAR ComparisonValue = CALCULATE(SUM(DataTbl[Balance]),DataTbl[Date] = ComparisonDate )

RETURN ABS(ComparisonValue - CurrentValue)

Monthly = VAR CurrentDate = MAX(DataTbl[Date])

VAR CurrentValue = CALCULATE(SUM(DataTbl[Balance]),DataTbl[Date] = CurrentDate )

var ComparisonDate = calculate(MAX(DataTbl[Date]),filter(DataTbl,DataTbl[Date]<CurrentDate))

VAR ComparisonValue = CALCULATE(SUM(DataTbl[Balance]),DataTbl[Date] = DATE(year(TODAY()),3,31) )

RETURN 

(ComparisonValue - CurrentValue)

Yearly = VAR CurrentDate = MAX(DataTbl[Date])

VAR CurrentValue = CALCULATE(SUM(DataTbl[Balance]),DataTbl[Date] = CurrentDate )

var ComparisonDate = calculate(MAX(DataTbl[Date]),filter(DataTbl,DataTbl[Date]<CurrentDate))

VAR ComparisonValue = CALCULATE(SUM(DataTbl[Balance]),DataTbl[Date] = DATE(year(TODAY()) - 1,12,31) )

RETURN

(ComparisonValue - CurrentValue)

Answer of the above code

[Anonymous]

Hi Pat,

Please find the attached pbix file for you reference. Could you please edit the dax query.

Thanks

Adi 

https://www.dropbox.com/sh/upleorbai3honsh/AADCD1bWbc5vlQtvmww5MuVea?dl=0