cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anupampandey
Helper III
Helper III

add YTD and MTD to sales figures in one table

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

1 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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @anupampandey,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.