Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have created a measure that will calculate the DELTA of a dynamic date table.
I am having a problem only when there is no data for a month in between the selected months. When there is no data for a month within the range of the MIN/MAX values, the number value is flipped (positive instead of negative, negative instead of positive).
For example, If we pick June and August to compare the Delta, using the following DAX formula (see below), we will get a value of 30.87. The value should be -30.87. Negative, not positive. This only happens when there is no data in a month in between the date selection. If I were to select August and September, or August and October, (or any months that do not have a data lapse, I receive the proper amount showing the proper negative or positive value.
Thoughts?
Here's the DAX I am using and two pictures. One, showing the value as a positive number (incorrect) and one showing the value as a negative numer (correct). The only difference is that July has no data... and as a result, inverts the integer for some reason.
Solved! Go to Solution.
@dyttam the issue is it's giving the min/max by a text value, which means it's going to be alphabetically for your months, no in the calendar order.
I would suggest adding a month column that is an integer, so that it will pick the month ordering instead.
this calculated column, add to your _Date table:
then modify your formula:
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@dyttam the issue is it's giving the min/max by a text value, which means it's going to be alphabetically for your months, no in the calendar order.
I would suggest adding a month column that is an integer, so that it will pick the month ordering instead.
this calculated column, add to your _Date table:
then modify your formula:
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@DataZoe Thank you for the response. I've added a month column that is an integer to my Date table and adjusted the DAX accordingly. This looks to be working as intended. Cheers!
@dyttam Awesome, glad to hear that it's working now!
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |