FTD & MTD Sales Tota

[Jeevan1991]

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.

[v-easonf-msft · Accepted 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.

[v-easonf-msft · Accepted 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.

[Jeevan1991]

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

[v-easonf-msft · Accepted solution]

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

[Jeevan1991]

Hi @v-easonf-msft 

Thank you so much its working fine. 

[Jeevan1991]

Dear Friends @Anonymous 

Please help me on the above.

[Jeevan1991]

Dear @amitchandak 

can you please help me on the above problem?