cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Urgent Help ---- DOD, MONTH, YEAR

Capture.JPGHi,

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

 

Urgent needed.

3 ACCEPTED SOLUTIONS

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Community Support
Community Support

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

Hi @G_93 

mahoneypat's suggestion should be helpful.

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

 

Best Regards

Maggie

View solution in original post

Highlighted
Helper I
Helper I

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

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
dod1.JPG

 

View solution in original post

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Community Support
Community Support

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

Hi @G_93 

mahoneypat's suggestion should be helpful.

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

 

Best Regards

Maggie

View solution in original post

Highlighted
Helper I
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

 

table.JPGVisual.JPGMeasure.JPG

 

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Helper I
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

Adi 

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

 

 

 

Highlighted
Helper I
Helper I

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

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
dod1.JPG

 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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.