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.
I wanted to create a KPI showing year to Date and Last year to date.
So I have Measures
Solved! Go to Solution.
After that Ive figured out what the issue is.
Here I have used Year which is an actual data column in my date table to slice the data with and its not working
This time I have used year from my date hierarchy and its worked
Which to me meas that if you are using these kind of time based measures, if you want to slice by date you HAVE to use the time period from the date hierarchy rather than what I have done and used the Year column created in the date dimension
Hi @DebbieE
try this
Ive changed to this logic but the same issue still stands
I'm actually struggling with the exact same issue. I think that I know what the problem is, but I don't know how to correct it.
When you select a year with your slicer, you're changing the filter context applied to the visual. SAMEPERIODLASTYEAR() will only "see" the table generated by the current filter context, which means it will only contain 2018 data. You can't calculate based on the previous year since there's no other year in the current filter context!
I'm using a date slicer instead of a year slicer, but I tried using something like this:
PY Sales = VAR CurrentDateMax = MAX([INVOICE_DATE]) VAR CurrentDateMin = MIN([INVOICE_DATE]) VAR EarliestDate = MIN(DateDim[Date]) VAR PYDateMax = IF(YEAR(CurrentDateMin) < YEAR(EarliestDate), EarliestDate, DATE(YEAR(CurrentDateMax)-1, MONTH(CurrentDateMax), DAY(CurrentDateMax)) ) VAR PYDateMin = IF(YEAR(CurrentDateMin) < YEAR(EarliestDate), EarliestDate, DATE(YEAR(CurrentDateMin)-1, MONTH(CurrentDateMin), DAY(CurrentDateMin)) ) RETURN CALCULATE( SUM([Net Ship $]), FILTER(ALL([INVOICE_DATE]), [INVOICE_DATE] >= PYDateMin && [INVOICE_DATE] <= PYDateMax ) )
The idea is that we can store the currently-selected max and min dates, subtract a year from them, and then use this range to filter your wide-open date table.
EDIT: I found a fix to my code. This works with my visual, including drilldown!
After that Ive figured out what the issue is.
Here I have used Year which is an actual data column in my date table to slice the data with and its not working
This time I have used year from my date hierarchy and its worked
Which to me meas that if you are using these kind of time based measures, if you want to slice by date you HAVE to use the time period from the date hierarchy rather than what I have done and used the Year column created in the date dimension
Can you show the relationship between dimDate and another table? Crossfilter are both or unique?
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |