Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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
Hi @anupampandey,
Can you provide the sample file and the formula which you used ? It will be help for troubleshooting.
Regards,
Xiaoxin Sheng
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
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
Hi
I have tried your suggestion but not get any value while computing the MTD.
Below are the points based on my dataset:
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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
133 | |
93 | |
90 |