Calculate DOS dynamically based on month

[Anonymous]

Hi,

Urgent help needed!

I have a table having Deal History coming in every month as below.

ReportMonth	Code	Deal_Date	Value
1/1/2020	A	1/12/2019	10
1/1/2020	A	23/11/2019	20
1/1/2020	A	3/11/2019	20
1/1/2020	A	15/10/2019	30
1/1/2020	A	8/9/2019	40
1/1/2020	B	2/11/2019	25
1/1/2020	B	3/12/2019	30
1/1/2020	B	5/10/2019	35
1/1/2020	B	8/9/2019	60
2/1/2020	A	1/12/2019	10
2/1/2020	A	23/11/2019	20
2/1/2020	A	15/10/2019	30
2/1/2020	A	1/9/2020	40
2/1/2020	B	2/11/2019	25
2/1/2020	B	3/12/2019	30
2/1/2020	B	1/10/2020	35
2/1/2020	B	5/10/2019	60

I need to  pivot for every month taking last 3 months values in consideration based on report month.

For ex:- below for 1/1/2020 i need sum values only for last 3 months from Jan i.e Oct, Nov and Dec.

same for 2/1/2020 ie for Feb-  sum of values for Nov, Dec and Jan. (ignore the inv column it is custom column)

Month	Code	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Inv
1/1/2020	A		30	40	10		20
1/1/2020	B		35	25	30		30
2/1/2020	A			20	10	40	25
2/1/2020	B			25	30	35	50

Query 2:- If i get the above table format, i need to calculate DOH, as -

-> for month - 1/1/2020 - if(inv<=Dec19, inv/dec19 *31,if(inv<=(dec19+nov19),(inv-dec19)/nov19*(30+31)),if(inv<=(dec19+nov19+oct19),(inv-dec19-nov19)/oct19*(31+30+31)

-> for month - 2/1/2020 - if(inv<=jan20, inv/jan20 *31,if(inv<=(dec19+jan20),(inv-jan20)/dec19*(31+31)),if(inv<=(jan20+dec19+nov19),(inv-jan20-dec19-nov19)/nov19*(31+30+31)

So 2 things are dynamic in above formula. 

1. Based on report month, formula will take last 3 months values in consideration.

2. the number 30 or 31 is number of days in the months taken into consideration for comparison.

Any help appreciated. !

[amitchandak]

@Anonymous , creata rolling formula with help of a date table

Rolling 3= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],startofmonth(Sales[Sales Date])-1,-3,MONTH))

or

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date])-1,-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak , thanks for your reply.

But i need to build this query in Query editor, because i need to do some processing of data and calculated column is not visible in query editor.

Is there any way for it?

Also can you please help with DOH calculation. ie. Query-2.

[parry2k]

@Anonymous You cannot have dynamic calculation using power query, given @amitchandak solution works, you need to use this in DAX and that will make it dynamic?

What is your thought process that you need it in Power Query?

[Anonymous]

I tried using the above formula, in it works in DAX. and it works to get the the running totals.

But my main concern is to calculate DOH using the running totals as below.

1. for month - 1/1/2020 -

=if(inv<=Dec19, inv/dec19 *31,if(inv<=(dec19+nov19),(inv-dec19)/nov19*(30+31)),if(inv<=(dec19+nov19+oct19),(inv-dec19-nov19)/oct19*(31+30+31)))

2. for month - 2/1/2020 -

=if(inv<=jan20, inv/jan20 *31,if(inv<=(dec19+jan20),(inv-jan20)/dec19*(31+31)),if(inv<=(jan20+dec19+nov19),(inv-jan20-dec19-nov19)/nov19*(31+30+31)))

and then i have a third table which needs to sumif the DOH for each code dynamically.