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
ADP007
Helper IV
Helper IV

TOTALYTD not working

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]))

 

26-04-2017 09-31-59.png26-04-2017 09-33-22.png

3 ACCEPTED SOLUTIONS
Dog
Responsive Resident
Responsive Resident

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. 

View solution in original post

Dog
Responsive Resident
Responsive Resident

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]))

View solution in original post

Dog
Responsive Resident
Responsive Resident

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.

View solution in original post

35 REPLIES 35
Anonymous
Not applicable

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.

Anonymous
Not applicable

Wait. Now I see.

The problem is that you don't use CalendarTable

Do you have one in your model?

@ADP007

 

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

Anonymous
Not applicable

@ADP007

Please see the attached .pbix file with 2 formulas working:

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

 

Michael

Anonymous
Not applicable

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?

Anonymous
Not applicable

@ADP007

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.

Anonymous
Not applicable

@ADP007

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.

Dog
Responsive Resident
Responsive Resident

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.

Dog
Responsive Resident
Responsive Resident

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

Dog
Responsive Resident
Responsive Resident

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

Dog
Responsive Resident
Responsive Resident

Hi David, 

 

is this the sort of thing you want.... 

 

dates kpi.PNG

 

let me know and I'll post the measure details

 

Dog
Responsive Resident
Responsive Resident

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]))

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.