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

Same result. Have the same also when using CALCULATE function.

Thanks

D.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Wait. Now I see.

The problem is that you don't use CalendarTable

Do you have one in your model?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

    Then use:

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

     

    Michael 

  • ADP007's avatar
    ADP007
    Helper IV
    9 years ago

    Hi,

     

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

    Same result.

     

     

  • avelio's avatar
    avelio
    Helper II
    9 years ago

    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's avatar
        Anonymous
        Not applicable
        9 years ago

        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