Table Creation

Question

I had create database as belowTypeType CategoryProduct CatProductYearMonthDateQuantityAmountSalesExtrenal SalesField related projectsAnimal slaughtering20191020-Oct-19201000SalesInternal SalesField related projectsAnimal slaughtering20191020-Oct-1960063765CostInternal SalesField related projectsAnimal slaughtering20191020-Oct-190-20020CostInternal SalesField related projectsAnimal slaughtering20191120-Nov-190-33080CostInternal SalesField related projectsAnimal slaughtering20191120-Nov-190-118430and then I create masurment like CurrentMonth/prevmonth/YTM/Yeartodate/prioryear month and varation for each as shown&nbsp;&nbsp;I want to have it in raw by typemean insted of product and product category I Need sales/cost/ocf/...... whish is &nbsp;

amitchandak · Accepted Answer

Sherifibrahim , you may not get the same format, but you can get the measure with help from calendar table 
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 Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
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")))
	
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

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"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"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))

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

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

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,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)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
CALCULATE([Total Value], PREVIOUSQUARTER('Calendar'[Date]))

&nbsp;
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-bihttps://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#M184Appreciate your Kudos.

Forum Discussion

Table Creation

1 Reply

Related content

Dynamic Table Creation???

Report creation

Semantic Model creation from Lakehouse tables

Oracle Upsert Least Privileges Needed for Copy Activity - Interim Table Creation

dynamic table creation

Type	Type Category	Product Cat	Product	Year	Month	Date	Quantity	Amount
Sales	Extrenal Sales	Field related projects	Animal slaughtering	2019	10	20-Oct-19	20	1000
Sales	Internal Sales	Field related projects	Animal slaughtering	2019	10	20-Oct-19	600	63765
Cost	Internal Sales	Field related projects	Animal slaughtering	2019	10	20-Oct-19	0	-20020
Cost	Internal Sales	Field related projects	Animal slaughtering	2019	11	20-Nov-19	0	-33080
Cost	Internal Sales	Field related projects	Animal slaughtering	2019	11	20-Nov-19	0	-118430