TOTALYTD not working

[ADP007]

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

[Dog · Accepted 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. 

[Dog · Accepted solution]

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

[ADP007]

HI Dog,

That is exactly what I need. Many thanks.

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

Thanks

D.

---

[ADP007]

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

[Dog]

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)

[ADP007]

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

[Dog]

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

[ADP007]

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

[Dog]

and just to play devils advocate. 

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

[ADP007]

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

[Dog · Accepted solution]

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.

[ADP007]

Great stuff. Thanks again.

[Dog]

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

[avelio]

@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 4 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]

Sure,

Upload them to your OneDrive and share the link

[ADP007]

Hi,

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

Same result.

[Anonymous]

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

Then use:

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

Michael