add YTD and MTD to sales figures in one table

[anupampandey]

Hi,

Is it possible to have these 2 calculated columns in one sales table? As of now I have created them but data for both the measure is coming same.

If month March selected than I need these as MTD show me sales from 1-03-17 to till date and YTD should show me 01-04-16 to till date. Anyhelp tip or help to resolve this.

Currently I doing the same by 2 tables one table is year till date table and second one is having data of current month only.

Thanks,

Anupam

[v-shex-msft]

Hi @anupampandey,

Can you provide the sample file and the formula which you used ? It will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[anupampandey]

Hi,

You can get the sample file from "https://drive.google.com/drive/folders/0B105l6qUOGQBWU5JUmpEUU9RTGc?usp=sharing"

I am using "MTDVal = calculation(sum(measure)),DATESMTD('Calendar'[DateKey]))

Regards,

Anupam Pandey

[CheenuSing]

Hi @anupampandey

Try the following

1. I assume you a master calendar table based on the min and max of dates in the transaction table.

2. What is your fiscal year beginning month. is it  Jan - Dec or Apr- Mar

3. Create a column in  calendar table

    MonthSeqNumber =  ([Year])*12 + [Month] - 1 )

    where Year and Month are column names in your calendar table.

4.  Create a measure 
              CurrentMonthSeqNumber = Year(Max([MasterCalendar[Date])) * 12 + Month (  Max([MasterCalendar[Date]) ) -1

5. Now the MTD mesure

   CALCULATE (
[SalesAmount] ,
FILTER (
ALL ( 'MasterCalendar' ),
'MasterCalendar'[MonthSeqNumber] = [CurrentMonthSeqNumber]
)
)

6. SalesThisYear = CALCULATE (
[SalesAmount] ,
FILTER (
ALL ( 'MasterCalendar' ),
'MasterCalendar'[Year] = MAX ( 'MasterCalendar'[Year] )
&& 'MasterCalendar'[Date] <= MAX ( 'MasterCalendar'[Date] )
))

IF this works for you please accept it as a solution and also give KUDOS.

Cheers 

CheenuSing

[anupampandey]

Hi 

I have tried your suggestion but not get any value while computing the MTD.

Below are the points based on my dataset:

1. Your first point I didn't understand (sorry I am new to PBI or data structure)
   1. In my transaction table all the dates are there. Their is a relationship between calender and sales table as "One to Many".
2. My fiscal year is Apr - Mar
3. I have created a calculated column and measure based on the fiscal year and fiscal month. Is this correct?

Sir, pls guide me on this. It would be of great help.

I am also sharing you the pbix file. You can download it from "https://drive.google.com/file/d/0B105l6qUOGQBNThEcFJpWnAxUVE/view?usp=sharing"

Regards,

Anupam

[CheenuSing · Accepted solution]

Hi @anupampandey

I am attaching the modified pbix file uploaded in one drive with the link

https://1drv.ms/u/s!ApP3mBZyGaHfzx1eUbQPh1KjnwRX

a) See how the Master Calendar has been developed.

b) See how the FiscalYearMonthName is set in the Sort by Column

c) See the MonthtoDateTotal and YearToDateTotals Measures.

    You will notice that YearToDateTotals has one more parameter in the DatesYTD function, "03-31" this signifies that the year ends for your datasets on 31 Mar each year.  Then the formula automatically intialises the value at the change of each Mar 31.

d) One more thing you should note is all the visuals you use like table,matrix,charts etc when you are chosing date as a dimension it should come from the MasterCalendar Table.

Hope this solves your issue. If so please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

[v-shex-msft]

Hi @anupampandey,

You can try to use below formula to calculate the MTD and YTD:

MTD = 
var curr=MAX(COMM_YTD[Date])
return
sumx(FILTER(ALLSELECTED(COMM_YTD),[Date]>=DATE(YEAR(curr),MONTH(curr),1)&&[Date]<=curr),[CALC_RET_COMM])

YTD = 
var curr=MAX(COMM_YTD[Date])
return
sumx(FILTER(ALLSELECTED(COMM_YTD),[Date]>=DATE(YEAR(curr),1,1)&&[Date]<=curr),[CALC_RET_COMM])

BTW, the result of calculated column/table not be affected by slicer, they are pretreatmented by formula. If you want the calculated result filtered by slicer, you need to use measure.

Regards,

Xiaoxin Sheng