Time Hierarchy, Calendar

[Anonymous]

Hi everyone,

I'm quite new on Power BI and DAX.
I have a database with column YEAR (2016, 2017, 2018...) and a MONTH column (01,02,03....10,11,12) but Power BI don't recognize it as date format.  

I need to calcule accumulate sales value and previous period growth (YTD and MAT) but for that i need, first, use the Calendar Dax.
What have i to do, to transform my data (Year and Month) on date format and with date, use Calendar function that i need for accumulate sales and PPG%?

Could you give me a suggestion/help?

Thank you so much,
Fernanda

[VasTg]

@Anonymous 

You could create a new date column with arbitrary date(1 or 10 or 15 or last day) and link that date column to your date dimension.

New_date = DATE(YEAR_COL,MONTH_COL,1)

DIMDATE = CALANDER(MIN(NEW_DATE),MAX(NEW_DATE)

If it helps, mark it as a solution

Kudos are nice too

[amitchandak]

Can you share a sample of the date table you have? Do you have months and years only or you have dates too?

[Anonymous]

Hi @amitchandak 

I just have months and years.

[amitchandak · Accepted solution]

Option 1 is that try the first two formulas.  Option 2 is concat month and year. And create a new table with month, year and concat month year (let this be date) and join this with you table using concatenated string. That will allow better filter 

//Option
This month=
var _mon = maxx(sales,sales[Month])
var _year = maxx(sales,sales[year])
return
calculate(sum(sales[amount]),all(sales[Month]),all(sales[year]),sales[mon] =_mon,sales[year] =_year)



last month=
var _mon1 = maxx(sales,sales[Month])
var _mon = if(_mon1=1,12,_mon1-1)
var _year = if(_mon1=1,maxx(sales,sales[year])-1,maxx(sales,sales[year]))
return
calculate(sum(sales[amount]),all(sales[Month]),all(sales[year]),sales[mon] =_mon,sales[year] =_year)
////////// Option 2
This month=
var _mon = maxx(date,date[Month])
var _year = maxx(date,date[year])
return
calculate(sum(sales[amount]),all(date),date[mon] =_mon,date[year] =_year)



last month=
var _mon1 = maxx(date,date[Month])
var _mon = if(_mon1=1,12,_mon1-1)
var _year = if(_mon1=1,maxx(date,date[year])-1,maxx(date,date[year]))
return
calculate(sum(sales[amount]),all(date),date[mon] =_mon,date[year] =_year)

Option 3 and the best one is create a date like

new date = date(table[year],table[month],1)

Now you can create a calendar table and use time intelligence function. Join on dates

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(sales,sales[date] <=maxx(date,date[date])))

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))  

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]

Option 3 works fine. Thank you so much 🙂