Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.