Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone! This community has been of wonderful help. I've been stuck on some date stuff and can't seem to get past it.
There are three measures I am looking to have: Current period sum, Previous period (Same length date range immediately preceding current period) sum, and Same Period Last year sum
The idea is that you would see the current sum with a % change for the previous period and Same Period Last Year next to it. I set this up by creating this measure in order to get the summation of a different date range
When using one date slicer, all of this works fine. The problem is that I am trying to add in preset dates along with the custom date slider. I followed this link https://www.linkedin.com/pulse/custom-range-date-slicer-power-bi-dynamic-defaults-more-a%C5%9F%C4%B1... to create this.
Everything in this link is the same as my model. When using the custom slicer option, everything again works fine. When I switch to a preset date, only the current period is calculated while everything else is "Blank" or doesn't show. I created measures to capture the date range of the previous period to see if something was off, but it properly reflects what the date range should be.
What am I missing? Is there a much easier way to compare different dates of the same measure? A different way to do preset dates with option for custom? The logic seems to be breaking in an unknown spot. Appreciate any help I can get!
Hi @PBIGuyL3,
Can you please share some dummy data that keeps the original data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Here are my main metrics. The idea is to see clicks based on current date range, clicks from the prior date range (same length time period immediately preceding current range), and a YoY using SAMEPERIODLASTYEAR. To get the comparison date range, I am taking the difference between Max and Min of the Current Date Range and subtracting it from those dates (if Current Range is 8/10 - 8/16, Comparison Range is 8/3-8/9). When the left date slicer (Custom Date) is being used, all the metrics work fine. When I switch to any of the preset date ranges, all my prior and YoY values go blank. I believe that the problem is that the dates to calculate are not being picked up. The bottom left date range in the graphic uses the same formula to calculate the date as the measures do, and it is able to accurately show the date range. Can't figure out why the prior and yoy measures won't show.
HI @PBIGuyL3,
AFAIK, infinity error should be caused by dividing a non-zero or non-null value by zero or null in DAX expression.
It means your formula has been filtered wrong ranges and the expressions try to divide with invalid values. Please check your period filter to confirm they can get correct results first.
Regards,
Xiaoxin Sheng
Have you considered using SAMEPERIODLASTYEAR etc, and letting your report users change the date filters in the filter pane instead of via slicers?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |