Forum Discussion

ADP007's avatar
ADP007
Icon for Helper IV rankHelper 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
ADP007's avatar
ADP007
Icon for Helper IV rankHelper 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

Dog's avatar
Dog
Icon for Responsive Resident rankResponsive Resident
9 years ago

Hi David, 

 

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

 

 

let me know and I'll post the measure details

 

  • Dog's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    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
    Icon for Responsive Resident rankResponsive Resident
    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
    Icon for Helper IV rankHelper IV
    9 years ago

    Great stuff. Thanks again.

  • ADP007's avatar
    ADP007
    Icon for Helper IV rankHelper IV
    9 years ago

    HI Dog,

     

    That is exactly what I need. Many thanks.

    Can you send me the report somehow? Mail? ([email protected])

     

    Thanks

    D.

  • ADP007's avatar
    ADP007
    Icon for Helper IV rankHelper IV
    9 years ago

    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's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

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

    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's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

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

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

     

  • Dog's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    and just to play devils advocate. 

     

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

     

     

  • ADP007's avatar
    ADP007
    Icon for Helper IV rankHelper IV
    9 years ago

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

  • Dog's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

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