Hey guys,
I’be been all day trying to search my error in this measure
Balance LY = CALCULATE([Balance], DATEADD(‘Tab Dates’[Date], -1, YEAR ) )
The Balance measure is like this:
Balance = SUM(‘Tab Main’[Balance])
in this pic beside, if i change the year to 2019 it will show the numbers. If i select 2020, also it will show but on the right, where you have the total for the period chosen, you also have that dateadd measure where it says P&L Values LY. And it’s not working. Any clue? Any ideas? Did you already had this?
In the left side table, what measure do you have ? Balance or, Balanace LY?
In the right side table “P&L values” and “P&L Values LY” are two different measures or, two different items of the same column , just like “Jan”, “Feb”, “Mar” in the left side table.
Now, let me give some answers, in general, which might help you out.
I guess, you don’t have any problem with the left side table and assuming you have chosen “2020”:
The values in the left side table will show the “Balance” for each chosen month of 2020, if the measure applied is “Balance”
The values in the left side table will show the “Balance LY” for each chosen month of 2019, if the measure applied is “Balance LY”
The “Total” would nothing but the sum of all the months.
Now, coming to the right side table.
Ideally, you should create 2 measures “P&L Values” and “P&L Values LY” (That will use the DATEADD).
If you not wish to do so and you have “P&L Values” and “P&L Values LY” as the elements of the same column then, use SWITCH() & SELECTEDVALUES() to create 2 different calculations in the same measure.
Let me know if this answers your question or, there is something else you wanted to know.
Hey @quantumudit
Thank you very much 4 trying to help me.
I have Balance measure and that’s why it works when i change the year in the filter
They’re 2 different measures
I won’t have any problems on the left side only if the measure inside P&L Values is Balance. Now, if i change to balance LY, it won’t work. Doesn’t show me any values. This DATEADD Balance LY measure doesn’t work.
Coming to the right side, actually, it doesn’t matter let or right as long as this measure is not in it, in a table or matrix.
I don’t have any filters on this page
I’ve done this visual in a new sheet
I’ve delete the Table dates
Still not working. It’s amazing! Don’t know what else to do
Thanks a lot for trying to help me
Dear @pedroccamara
I think that I have been in a situation like this. In my case, I just needed to add a “.[Date]” to the formula like what I’ve done in the following formula:
Balance LY = CALCULATE([Balance], DATEADD(‘Tab Dates’[Date].[Date], -1, YEAR ) )
would you please test it and let me know if it works for you?
Welcome to the community and thanks sharing your experience
Just want to emphasize that if you have to add a “.[Date]” you are actually using the Auto Date/Time (and I’m really hesitant to call it a “feature”). When that option is enabled, Power BI Desktop creates a hidden auto date/time table for each date column in your Model. Which can have a real impact on file size and performance.
So as best practice.
Turn Auto Date/Time off in the global setting of Power BI.
Always incorporate a proper Date table - if your model includes date fields
Mark that Date table as a Date table (option in the Table Tools ribbon)
Hello @sedhosen
Thanks for the answer by i can’t add this date field to the measure.
Maybe because i have auto Date/time turned off and/or my date table is marked as a date table.
I don’t know.
Thanks anyway
Guys, thank you so much for trying to help me. I’m attaching the file.
Please look at the P & L sheet. The idea is to have 2019 values when i choose 2019 year above and the same for 2020, along with the months that i choose.
Thank you thank thank youOxray DNA.pbix (1.0 MB)
