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

HI Dog,

 

That is exactly what I need. Many thanks.

Can you send me the report somehow? Mail? (adp@skynet.be)

 

Thanks

D.


Hi Dog,

 

It looks great except the Previous Full Year Sales when there is no data for the previous year. It show some value.

 

Also can I add other slicers that will interact with the calculation ? For example BusinessAgency ?

 

Thanks for your great help

 

27-04-2017 10-00-04.png

Dog
Responsive Resident
Responsive Resident

assuming the business agency stuff is in a linked table (or the same as salenet value) then yep additional slicers will work fine. 

 

re. the blank values just check for within an IF 

 

Previous Full Year Sales = CALCULATE([Current Full Year Sales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))

 

becomes (i used variables I find it easier to read) 

 

Previous Full Year Sales =

var PFYS = CALCULATE([Current Full Year Sales], SAMEPERIODLASTYEAR(DimDate[BK_Calendar]))

RETURN

if(ISBLANK(PFYS), 0, PFYS)

 

Many thanks again

 

It's not the blank values that are an issue, it's the 479.11M. It should also show a blank value. No idea what this value is.

 

Thanks

 

 

27-04-2017 10-01-49.png

Dog
Responsive Resident
Responsive Resident

Ah ok. 

 

do you know if that one is working at all?  so if you select "2016" does the previous year value change to 179.56?

 

Thanks

Works fine , just when there is no data for previous year it show some strange value.

 

27-04-2017 10-42-38.png27-04-2017 10-42-52.png

Dog
Responsive Resident
Responsive Resident

and just to play devils advocate. 

 

can you send a shot of the 2014 year selected as well please? 

 

 

I don't have data for 2014 in my result set. In my query I select only 2015,2016,2017 & 2018.

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.

Great stuff. Thanks again.

Dog
Responsive Resident
Responsive Resident

No that's fine I just wanted to see what the Current Full Year card came out like. 

 

 

@ADP007 i'm using something like this. Maybe it helps

 

This Month Sales =
VAR
CurrentDate = TODAY()
RETURN
CALCULATE(SUM('Table'[Sales]) ,(DateKey[Month number] = MONTH(CurrentDate)-1), DateKey[Year] = YEAR(CurrentDate))

 

You can play from here.

For example if you want last month sales, you just add -1 after MONTH(CurrentDate) (i added it with bold)

 

I think if you replace MONTH(CurrentDate)-1) with will work just fine (to retrieve april sales)

 

Also, to retrieve same period sales but for previous year you just add a -1 after YEAR(CurrentDate):

 

This Month Sales -1 Year =
VAR
CurrentDate = TODAY()
RETURN
CALCULATE(SUM('Table'[Sales]) ,(DateKey[Month number] = MONTH(CurrentDate)), DateKey[Year] = YEAR(CurrentDate)-1)

 

Try it out.

 

BR,

Andrei

Anonymous
Not applicable

Sure,

Upload them to your OneDrive and share the link

Hi,

 

I tried also usuing a dimDate table joined with a 1-to-many relationship. So two tables.

Same result.

 

 

26-04-2017 10-09-54.png26-04-2017 10-10-13.png

Anonymous
Not applicable

You need to use the CaledarTable that is connected to your Query1 table.

Then use:

YTD = TOTALYTD(sum(Query1[SaleNetNet]);CalendarTable[Date_dt])

 

Michael 

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.