Forum Discussion

ADP007's avatar
ADP007
Helper IV
9 years ago
Solved

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...
Need Help
  • Dog's avatar
    Dog
    9 years ago

    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's avatar
    Dog
    9 years ago

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

  • Dog's avatar
    Dog
    9 years ago

    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's avatar
ADP007
Helper IV
9 years ago

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

avelio's avatar
avelio
Helper II
9 years ago

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