Forum Discussion

acco's avatar
acco
Frequent Visitor
9 years ago

problem with slicer and dynamic dates

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

Need Help

10 Replies

  • acco's avatar
    acco
    Frequent Visitor
    9 years ago

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

    • MFelix's avatar
      MFelix
      Super User
      9 years ago

      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's avatar
        acco
        Frequent Visitor
        9 years ago

        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