Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have a sales table like the below.
Zone | Date | Month | Sales |
East | 23-Sep-2020 | 9.00 | 100 |
West | 16-Sep-2020 | 9.00 | 189 |
South | 17-Sep-2020 | 9.00 | 299 |
North | 16-Sep-2020 | 9.00 | 189 |
East | 11-Sep-2020 | 9.00 | 107 |
West | 27-Aug-2020 | 8.00 | 186 |
South | 12-Sep-2020 | 9.00 | 265 |
North | 19-Sep-2020 | 9.00 | 219 |
East | 02-Sep-2020 | 9.00 | 134 |
West | 02-Sep-2020 | 9.00 | 235 |
South | 31-Aug-2020 | 8.00 | 232 |
North | 17-Sep-2020 | 9.00 | 173 |
East | 03-Sep-2020 | 9.00 | 107 |
West | 23-Sep-2020 | 9.00 | 198 |
South | 20-Aug-2020 | 8.00 | 105 |
North | 25-Aug-2020 | 8.00 | 272 |
East | 15-Sep-2020 | 9.00 | 181 |
West | 02-Sep-2020 | 9.00 | 258 |
South | 23-Sep-2020 | 9.00 | 281 |
North | 23-Sep-2020 | 9.00 | 238 |
Now I wanto create FTD & MTD summary like the below.
FTD = 23rd Sep 2020 (The latest date)
MTD = Entire Sep (9) month
Zone | FTD Sales | MTD |
East | 100 | 629 |
West | 198 | 880 |
South | 281 | 845 |
North | 238 | 819 |
Total | 817 | 3173 |
Please help me on this to create a measure in power Bi.
Solved! Go to Solution.
Hello, @Jeevan1991
Try the following measure:
FTD = DATE(2020,9,23)
// IF your latest day is today,try formula as below:
// FTD =today()
Measure FTD_SALE = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]='Table'[FTD] ))
MTD_SALE = CALCULATE(SUM('Table'[Sales]),DATESMTD('Table'[Date]))
The result will be displayed as shown below:
For more details, check the attachment.
Best regards
Community Support Team _ Eason
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hi , @Jeevan1991
If you want to use function "lastdate",you need to consider the current context in measure FTD_SALE:
You can try the formula as below:
Measure FTD_SALE =
VAR ftd =
LASTDATE ( 'Table'[Date] )
RETURN
CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[Date] = ftd ) )
You also can change the formula FTD as below:
FTD = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Best Regards,
Community Support Team _ Eason
Hello, @Jeevan1991
Try the following measure:
FTD = DATE(2020,9,23)
// IF your latest day is today,try formula as below:
// FTD =today()
Measure FTD_SALE = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]='Table'[FTD] ))
MTD_SALE = CALCULATE(SUM('Table'[Sales]),DATESMTD('Table'[Date]))
The result will be displayed as shown below:
For more details, check the attachment.
Best regards
Community Support Team _ Eason
If this post helps,then consider Accepting it as the solution to help other members find it faster.
If i use LASTDATE function to identify FTD date then it gives me the same total as the MTD sales only.
FTD Date = LASTDATE(<Date Column>)
Hi , @Jeevan1991
If you want to use function "lastdate",you need to consider the current context in measure FTD_SALE:
You can try the formula as below:
Measure FTD_SALE =
VAR ftd =
LASTDATE ( 'Table'[Date] )
RETURN
CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[Date] = ftd ) )
You also can change the formula FTD as below:
FTD = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Best Regards,
Community Support Team _ Eason
Dear Friends @Anonymous
Please help me on the above.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |