How to dynamic show the current date , then use the current date to get actual target pipeline

[Anonymous]

Hello All, I couldn't figure out a few questions. Will be very appreciate if you can provide some insights. I have an extract dataset.  this a mock data, no rules to follow. the real data has 12 KPI's , each has actual , target, pipeline value,  here I only show 1 KPI.

challenge I am facing is , this is a extract file, the current date is 1/1/2020, the latest date will change when new extract version come in, so I dont want to use filter.

I want to show:

1, a dynamic way to show the latest date that has actual value, in this case will be 1/1/2020, maybe in the future, the extract file will show another different date. I don't want to use filter manually.

2, a dynamic way to show the actual value , target , pipeline that related to this date, in this case is 1/1/2020.

3, a dynamic way to show last month or same month from last year value that related to the current date, in this case it is 1/1/2020

4, a way to show cumulative value from current value, last month, or the same month from last year.

Sorry for lots of questions. I am new for POWER BI, looking forward to learn more from this community

DATE	ACTUAL 1	ACTUAL 2	TARGET 1	PIPE LINE 1
1/1/2019	97747.9	1234.4	9666.3	10311.5
2/1/2019	107757.2	1111.3	10110.7	10671.5
3/1/2019	97747.9	2113.5	10555.1	11031.5
4/1/2019	37692.1	1555.2	10999.5	11391.5
5/1/2019	47701.4	1994.75	5666.7	9591.5
6/1/2019	57710.7	2191.21	6111.1	9951.5
7/1/2019	47701.4	2387.67	6555.5	5271.5
8/1/2019	33688.38	2584.13	5222.3	5631.5
9/1/2019	25680.94	2780.59	5666.7	5991.5
10/1/2019	17673.5	2977.05	6111.1	6351.5
11/1/2019	9666.06	3173.51	6555.5	6711.5
12/1/2019	1658.62	3369.97	6999.9	7071.5
1/1/2020	6348.82	3566.43	7444.3	7431.5
2/1/2020	0		5188.11538	7791.5
3/1/2020	0		4856.03626	4555.8
4/1/2020	0		4523.95714	4399.3
5/1/2020	0		4191.87802	4228.1
6/1/2020	0		3859.7989	2532
7/1/2020	0		3527.71978	1447.33
8/1/2020	0		3195.64066	362.66
9/1/2020	0		2863.56154	3200
10/1/2020	0		2531.48242	3200
11/1/2020	0		2199.4033	2400
12/1/2020	0		1867.32418	2000

[amitchandak]

When you want default date, you use relative date slicer or these option s

https://community.powerbi.com/t5/Desktop/Set-TODAY-as-default-date-in-slicer-visual/td-p/488670

https://www.youtube.com/watch?v=lkHFpmA4SJ4

Also, you can create a column flag of today and filter on that.

Flag = if(date[date] =today(),"Yes","No")

Have a date dimesnion table and join it with date. Post that you can use time intelligence functions.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))





YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM('Fact table'[Sales]),filter(date,date[date] <=maxx(date,date[date]) 
	&& weekday(sales[date]) <=weekday(maxx(date,date[date])) ))

Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31"))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  
Rolling last 1 before 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Maxx('Date',dateadd('Date'[Date],-1,MONTH)),-1,MONTH))  
	Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-1,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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak  Hi, thanks for the detail reply.

About the date, I did not want to get today's date, the latest date in data is 1/1/2020, not 2/6/2020. that is the date I need to get.

Once I have that date,I assume I could get the actual , target and pipeline value for that same date. I prefer to not use slice or filter, is there a way to have formula to get 1/1/2020 , and actual value? thank you

[amitchandak]

This is what you can try when you create you calendar. create it till max date of your fact.

Date = Calendar(minx(sale,sales[sale date]),maxx(sale,sales[sale date]))

This will stop last date at the last transaction and all-time intelligence functions should work from that date. As we are passing  Date[Date] to those.

Hope this will help

[Anonymous]

@amitchandak  thank you Amit. I tried, it is not working. these information are in one table. meaning the actual and target share one date. If I use calendar, the max date still is 12/1/2020, not 1/1/2020. Is there a way to use filter and this formula? thanks again

[amitchandak · Accepted solution]

Check this.

https://www.dropbox.com/s/cml0oawqxrs12ma/dynamicCurrentDate.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak Thank you so much !!!