cancel
Showing results for
Did you mean:
Highlighted Helper I

## Urgent Help ---- DOD, MONTH, YEAR Hi,

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

Urgent needed.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted Super User VI

## Re: 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

Proud to be a Super User!

Highlighted Community Support

## Re: Urgent Help ---- DOD, MONTH, YEAR

Hi @G_93

Best Regards

Maggie

Highlighted Helper I

## Re: Urgent Help ---- DOD, MONTH, YEAR

Hi Pat,

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) 6 REPLIES 6
Highlighted Super User VI

## Re: 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

Proud to be a Super User!

Highlighted Community Support

## Re: Urgent Help ---- DOD, MONTH, YEAR

Hi @G_93

Best Regards

Maggie

Highlighted Helper I

## Re: Urgent Help ---- DOD, MONTH, YEAR

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   Highlighted Super User VI

## Re: Urgent Help ---- DOD, MONTH, YEAR

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

Proud to be a Super User!

Highlighted Helper I

## Re: Urgent Help ---- DOD, MONTH, YEAR

Hi Pat,

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

Thanks

Highlighted Helper I

## Re: Urgent Help ---- DOD, MONTH, YEAR

Hi Pat,

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) Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (815)