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,

 

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

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

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

    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 :smileyfrustrated: 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... :smileysad:

     

    Example file

     

    Best Regards

    Fabio