Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Really can't figure out why TOTALYTD is not working. Have seen the tutorial, read the posts here but can't get it to work.
I'm trying to have a running sum for each month.
Any help is more than welcome
David
YTD = TOTALYTD(sum(Query1[SaleNetNet]);Query1[InsertionDate_dt];all(Query1[InsertionDate_dt]))
Solved! Go to Solution.
Hi @ADP007
sorry I haven't got one drive account from work.
in the pbix file, i went into the relationship area and deleted the relationship you had between FK_Calendar and SK_Calendar and created a new relationship between the BK_Calendar columns.
does that help?
Dog.
just in case I'm not online I'll post anyways
both filters are set against the Year and MonthNameEN columns from DimDate table
SumOfSales = sum(FactEstimates[SaleNetNet])
Current Period Sales = CALCULATE([SumOfSales])
Previous Year Period Sales = CALCULATE([SumOfSales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))
Current YTD Sales = CALCULATE([SumOfSales], DATESYTD(DimDate[BK_Calendar]))
Previous Year YTD Sales = CALCULATE([Current YTD Sales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))
Current Full Year Sales = CALCULATE([SumOfSales], ALL(DimDate), DATESBETWEEN(DimDate[BK_Calendar], STARTOFYEAR(DimDate[BK_Calendar]), ENDOFYEAR(DimDate[BK_Calendar])))
Previous Full Year Sales = CALCULATE([Current Full Year Sales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))
Hi David,
the previous full year is returning a total of all salesnet for 2014 as there are no dates in the date table.
there are two options I suppose, you could populate those 2014 dates in the DimDate table which would return a blank for the joined records.
or
amend the measure to manually work out the start and end dates of the previous year and only return data if these are populated.
Previous Full Year Sales =
var startdate = DATEADD(STARTOFYEAR(DimDate[BK_Calendar]), -1, YEAR)
var enddate = DATEADD(ENDOFYEAR(DimDate[BK_Calendar]), -1, YEAR)
RETURN
IF(ISBLANK(startdate) || ISBLANK(enddate),blank(), CALCULATE([SumOfSales], ALL(DimDate), DATESBETWEEN(DimDate[BK_Calendar], startdate, enddate)))
Dog.
Try without the last part:
YTD = TOTALYTD(sum(Query1[SaleNetNet]);Query1[InsertionDate_dt])
Same result. Have the same also when using CALCULATE function.
Thanks
D.
Wait. Now I see.
The problem is that you don't use CalendarTable
Do you have one in your model?
Try this so create a date table:
DateKey = CALENDAR(DATE(2012,01,01),DATE(2017,06,30))
Then I add in a new column from the ribbon for each of the following:
Year = YEAR(DateKey[Date])
Month number = MONTH(DateKey[Date])
Month = FORMAT(DateKey[Date],"MMM")
Day = FORMAT(DateKey[Date],"ddd")
Week = WEEKNUM(DateKey[Date],1)
Quarter = "Q" & ROUNDUP(MONTH(DateKey[Date])/3,0)
MonthY = FORMAT(DateKey[Date],"MMM")&" " &DateKey[Year]
Day number = DAY(DateKey[Date])
Financial year = IF(DateKey[Month number]>6,DateKey[Year]+1,DateKey[Year])
Financial week = IF(DateKey[Month number]>6,DateKey[Week]-26,DateKey[Week]+26)
Total days = DAY(DATE(DateKey[Year],DateKey[Month number]+1,1)-1)
Financial month number = IF((DateKey[Month number]-6)<=0,DateKey[Month number]+6,DateKey[Month number]-6)
Financial month = FORMAT(DateKey[Date],"MMM")
Index = CALCULATE(COUNT(DateKey[Date]),ALL(DateKey[Date]),FILTER(DateKey,DateKey[Date]<=EARLIER(DateKey[Date])))
Monthy number = DateKey[Year]&DateKey[Month number]
I also include the following measures:
Measures
Current financial week = IF(WEEKNUM(NOW())-26<=0,WEEKNUM(NOW())+26,WEEKNUM(NOW())-26)
Current financial month = IF(MONTH(NOW())-6<=0,MONTH(NOW())+6,MONTH(NOW())-6)
Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))
Then I can use these rolling filters:
Other formula
+/- 1 months = IF(AND(DateKey[Financial month number]>=[Current financial month]-1,DateKey[Financial month number]<=[Current financial month]),1,0)
Last 30 day = IF(AND(DateKey[Date]>=[Today]-30,DateKey[Date]<=[Today]),1,0)
Last week + two weeks = IF(AND(DateKey[Financial week]>=[Current financial week]-1,DateKey[Financial week]<=[Current financial week]+2),1,0)
Last 14 days = IF(AND(DateKey[Date]>=[Today]-14,DateKey[Date]<=[Today]),1,0)
+/- 30 days = IF(AND(DateKey[Date]>=[Today]-30,DateKey[Date]<=[Today]+30),1,0)
And then you can use the following YTD formula:
YTD = CALCULATE(SUM('Tablename'[Column]) / 4.3 ,DATESYTD(DateKey[Date].[Date]))
YTD Last Year (limited by last date in Data table) =
VAR DataMaxDate =
CALCULATE ( MAX ( 'TO'[Date] ), ALL ( 'TO' ) )
RETURN
CALCULATE (
[YTD],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( DateKey[Date].[Date] ),
DATESBETWEEN ( DateKey[Date].[Date], BLANK (), DataMaxDate )
)
)
)
BR,
Andrei
Please see the attached .pbix file with 2 formulas working:
https://1drv.ms/u/s!AoP_9ampPIT7jQbxf_ttuNwBySj6
Michael
I think I know why your report is not working.
Did you use MonthNumber from for Fact? (Query1)?
If so - you need to use MonthNumber from your DimDate table in your report
Michael
Can I share my reports with you? If so how can I do this?
I see that I cannot download .pbix files from external sources in my organization so I could lookat those only in the evening.
Meanwhile - did you put the MonthNumber from your DimDate table in your report?
It should work
Michael
Hi, yes I did. Same result. Thanks for your help.
Try changing this:
all(Query1[InsertionDate_dt])
to this:
all(Query1)
in your original formula
If this doesn't work - I'll look at your files in the evening
Michael
Hi , that doesn't work either. Many thanks.
Hi,
I've always related my dates table on the actual date, I've downloaded your model and changed the relationship and now hte YTD function is working for me.
can you try?
Dog
HI Dog,
Can you share your solution with me? Not sure I understand exactly what you did.
Thanks
D.
Hi @ADP007
sorry I haven't got one drive account from work.
in the pbix file, i went into the relationship area and deleted the relationship you had between FK_Calendar and SK_Calendar and created a new relationship between the BK_Calendar columns.
does that help?
Dog.
Hi ,
Fantastic it works.
Many thanks for your help.
David
no problem - glad it worked 🙂
Do you have any advice on this? What I'm trying to have is a slicer on a date & month and showing 6 KPI's
Example I select : April 2017 (2017/04)
KPI 1 shows : sales for April 2017
KPI 2 shows : sales for April 2016
KPI 3 shows : sales January to April 2017
KPI 4 shows : sales January to April 2016
KPI 5 shows : Total for year 2017
KPI 6 shows : Total for year 2016
I have this in a multidimensionnal cube with calculatd measures and it works fine in Excel. Just can't seem to get what I need in PowerBI
Thanks
David
Hi David,
is this the sort of thing you want....
let me know and I'll post the measure details
just in case I'm not online I'll post anyways
both filters are set against the Year and MonthNameEN columns from DimDate table
SumOfSales = sum(FactEstimates[SaleNetNet])
Current Period Sales = CALCULATE([SumOfSales])
Previous Year Period Sales = CALCULATE([SumOfSales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))
Current YTD Sales = CALCULATE([SumOfSales], DATESYTD(DimDate[BK_Calendar]))
Previous Year YTD Sales = CALCULATE([Current YTD Sales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))
Current Full Year Sales = CALCULATE([SumOfSales], ALL(DimDate), DATESBETWEEN(DimDate[BK_Calendar], STARTOFYEAR(DimDate[BK_Calendar]), ENDOFYEAR(DimDate[BK_Calendar])))
Previous Full Year Sales = CALCULATE([Current Full Year Sales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))