problem with slicer and dynamic dates

[acco]

Hi everyone, I'm quite new to Power BI (but I love it).
I've my data, that consist in a single table with lots of columns. The data rappresents the income, or better the insurance contracts paid by our customers. For each record there are several dates, two of them are used to filter the data (the date of end of cover of an insurance and the date corresponding the day a customer has paid).
I've imported only last 4 years of data.
Now I've created a slicer with YEARS and another one with MONTHS to dynamically show the data. I've to consider a special case about the running year cause it often happen that a customers pay an insurance which has just ended otherwise it will in the future, so, to compare income of the current year with the income, based on the same period, of last year. First of all I use MAX function to find the most recent day in which we have registered income. Based on that I set two dates: the starting day of actual year (the 01 january YYYY) and the "last payment day". With calculate() and sum(), filtering with datebetween() the two dates described before I obtain the total income for the year selected.
Now I would obtain the same total BUT for the previous year (so from 1st january YYYY-1) but I can not. The problem seems to be in passing the starting date, I've tried with date(), dateadd() but nothing to do, if I enter the date as a text string everything works as aspected but it can not be dynamically (if I choose one, or two, different years, the starting date has to be dynamically finded).
Any suggestion will be very appreciated.

ByeBye
Fabio

[acco]

noone has some ideas?!? I've spent days on this problem without resolve it... 

[MFelix]

HI @acco,

Are you using the TOTALYTD formula? If you used this one on the you can then use the DATEADD formula to use with the dinamic date you calculate and select the -1 Year to have your previous year calculation.

Regards,

MFelix

[acco]

Hi Felix,

Thanks for your replay! Yes I've tried to use TOTALYTD and it works at 90%. The problem is: in the current year, 2017, it's not rare for a customer pay an insurance, which has date of end cover in the future, months too. So for current year it could be not a problem but it'll be for calculation of "last year" cause TOTALYTD LastYear will return the sum of everything till at the last date. So I will not have a correct value of comparison.

Example: today is 22nd of march 2017, a customer pay an insurance that expires at the end of august. For TOTALYTD it's a minor problem but considering "last year" the function will sum EVERYTHING between 1st january of 2016 and the end of august 2016!

I could use, just for current year, another kind of date to limit the data to consider: collecting's date, in my example is today 22nd march 2017 but I dunno ho to use to limit in current year and last year...

More suggestions?!?

Thanks
Fabio

[MFelix]

Try to use the dateadd function for end date havong day and month equal to the current date (22nd) and the year place in as current -1.

You can also use the Parallelperiod i thunk this is the formula name (not near computer) to have last year same timeframe look it up in the forum or google it for a full detail of how it runs.

Once again sorry for sligth detail but i'm on my cellphone.

Mfelix

[acco]

You mean as 4th parameter of TOTALYTD function? I've tried a second ago but I obtain silly numbers. I've used on the current year expecting a total just a bit less (cause it should not count future payments instead it returns only a fraction of what it should).

[MFelix]

Hi @acco,

Just did a siomple file with dates starting from 01/01/2016 until 31/12/2019 and added a value for each year I add 1 so all dates in 2016 are 1, 2017 are 2, 2018 are 3, so forth. Then I added this two measures:

YTD = TOTALYTD(SUM(Table1[Value]);Table1[Date])
YTDPY = TOTALYTD(SUM(Table1[Value]);DATEADD(Table1[Date];-1;YEAR))

This gives me the total of value for each year based on the filters I have, so if you count the days between 01/01 and 22/03 you have for 2016 - 82 days and for 2017 - 81 so taking into account that the value double we have a result of 82 for PY and 162 for Current Year as you can see in the print below, please adapt the formulas above to your requisitions.

Regards,

Mfelix

[acco]

Thanks for your answer and your time.

To clear my situation I've created an example:

I've recreated a simple set of my data, with the 3 columns involved in this calculation.

All my charts are designed on "DataEndCover" (red box). In the example I use the TOTALYTD function and I obtain a quite precise result (140 euro), quite because analizing the last date about "PaymentDay" (small green box), that is 22nd march 2017, there are 2 items paid (small purple box) which have subsequent date.
So when I create the TOTALYTD for LastYear the result is wrong because it will consider all the item between 1st january 2016 and 11 june 2016 (big purple box) BUT the real range should be from 1st january 2016 to 22 march 2016 (big green box).

To have the most correct data I should limit, in current year and in last year, all items that have "EndDayCover" from 1st january to 22 march.
There is a way to get it?

[MFelix]

Hi @acco,

I think something is missing in my perception of your problem because I have made a sample with the date you show and if I do the following measures they return the correct result, however my slicer are made based on PaymentDay, should they be based on CoverEndDay?

YTD = TOTALYTD(SUM(Table1[Income]);Table1[PaymentDay])

YTD-LY = TOTALYTD(SUM(Table1[Income]);DATEADD(Table1[PaymentDay];-1;YEAR))

Can you plewase prepare a PBIX with the data and slicers that you want so that I can check if I'm not calculating everything as you need.

Regards

Mfelix

[acco]

Hallo @MFelix, This morning I've found a solution (extremely commonplace...) that consists to use the 3rd parameter passing it as filter the FILTER function with MAX(PaymentDay) so it give it to me a correct value for current year and last year (anyway I've attached a example pbix file to check).
But it's not over yet in my idea I would insert another object to show, month by month, the difference of total income now and in the same period past year. If I use the measure, that consist in the difference of this year less previous year, Power BI return an error telling that the measure, with DATEADD, should have contigous dates... 

Example file

Best Regards

Fabio

[MFelix]

I don't know how you set up your model and dates but the Totalytd gives the values from 1 jan to date selected on slicer when you place the previous year in the formula start and end date ut should give you the previoud year value.

Again not on my computer cannot show with measures.


Mfelix