Urgent Help ---- DOD, MONTH, YEAR

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

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

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