Forum Discussion
DAX: Sumifs
Hi All..I need helped to write a dax statement for my dataset sample below
| Market | Period | Sales |
| AUSTRALIA | 201801 | 100 |
| CANADA | 201801 | 100 |
| CZECH REPUBLIC | 201801 | 100 |
| FRANCE | 201801 | 100 |
| GREAT BRITAIN | 201801 | 100 |
| INDIA | 201801 | 100 |
| POLAND | 201801 | 100 |
| USA | 201801 | 100 |
| AUSTRALIA | 201802 | 100 |
| CANADA | 201802 | 100 |
| CZECH REPUBLIC | 201802 | 100 |
| FRANCE | 201802 | 100 |
| GREAT BRITAIN | 201802 | 100 |
| INDIA | 201802 | 100 |
| POLAND | 201802 | 100 |
My data has 3 fields: Country, Time Period(yyyymm) and Sales. I need to create two columns 6MMT Sales and YTD Sales.
6MMT Sales would be sales of 6 prior months: Ex: Australia 201901 sales would be sum of sales from 201807 to 201812.
The two constraints here are Country and Time Period.
6MMT Sales would be sales of 6 prior months: Ex: Australia 201901 sales would be sum of sales from 201807 to 201812.
YTD Sales (201904) is sum of sales (201812 to 201903).
Please can some one guide me with this.
3 Replies
You can create date field using YYYYMM
Date = date(left([period],4),right([period],2),1)
Now use a date calendar and time intelligence
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH)) Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-6,MONTH)) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) //change end date of year This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) //change end date of year Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) //change end date of year Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) //change end date of year Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) //change end date of year Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))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/Amit thanks for your help. However, either I haven't implemented it properly or there is something wrong here. I had a date field in the sales table and in the calendar table I have day level data (YYYYMMDD). So when I used the DAX for each row I am getting the sales for the same month. Am I missing something here?
