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 ...
Need Help
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

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

      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's avatar
        MFelix
        Super User
        9 years ago
        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