Year to date with slicer

pledington · ‎08-22-2017

Hi, I'm new to Power BI and hope someone can help me with a problem I have calculating a Year to Date value. I get the correct number sometimes but some dates I select in my slicer I get the wrong answer.

Our Financial year is 31st March, I'm trying to create a table that has our year to date sale number based on a date selection from a slicer, if I choose any date up to Jul-17 I'm getting the right answer but After that e.g. Aug-17 I get a smaller number than Jul-17, not bigger. I think it's because of the filter on the slicer and tried to use the All function but it doesn't like it.

My slicer is based on the table/column called 'Sales Master' [RRD] that is in date format on the query.

I have over 4 years of sales data and want to be able to get the YTD number based on any date selection e.g. if the slicer is Jul 17 then it's from Apr-17 to Jul-17, if I select Feb-17 then i want from Apr-16 to Feb-17.

I created a new measure with the following :

USD.YTD = calculate(sum('Sales Master'[USD.A]);
   DATESBETWEEN('Sales Master'[RRD];
    DATE(IF(MONTH(LASTDATE(DATEADD('Sales Master'[RRD];0;MONTH)))>3;
     YEAR(LASTDATE(DATEADD('Sales Master'[RRD];0;MONTH)));
     YEAR(LASTDATE(DATEADD('Sales Master'[RRD];0;MONTH)))-1);4;1);
    LASTDATE(DATEADD('Sales Master'[RRD];0;MONTH))
  ))

Not sure if I just need to/possible to change the 2nd line above to something like: DATESBETWEEN(ALL('Sales Master'[RRD]); or a more simple approach to this?

I also know I need to do some error handling on this e.g. if the slicer has no selection it returns something funny rather than the entire total.

Any help pointing me in the right direction is much appreciated

Thanks

v-huizhn-msft · ‎08-28-2017

Hi @pledington,

Your date filed in resource table is continuous or not? If not, you should create a calendar table, relate the calendar table to 'Sales Master' table by [date] field. Then you can try the fomula below.

USD.YTD =
IF (
    MONTH ( LASTDATE ( DATEADD ( 'Sales Master'[RRD], 0, MONTH ) ) ) > 3,
    CALCULATE (
        TOTALYTD ( 'Sales Master'[RRD], 'Calendar'[DATE] ),
        DATEADD ( 'Calendar'[Date], -3, MONTH )
    ),
    CALCULATE (
        TOTALYTD ( 'Sales Master'[RRD], 'Calendar'[DATE] ),
        SAMEPERIODLASTYEAR ( Calendar[Date] )
    )
)

If this still returns uncorrect result, could you please share your sample table for further analysis?

Best Regards,
Angelia