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 folks,
I'm struggling with this one and couldn't find my way out
My goal is to show a Bar chart with current year months on X-axis, and monthly Sales of current/last year on values.
for that I created a measure consists of the SAMEPERIODLASTYEAR function (for last year values)
everything works fine but with one little bug:
On the 1st day of every month, the SAMEPERIODLASTYEAR function returns the sales for a single day (the 1st of same month last year) instead of the entire month
from the second of the month and further, there is no problem.
for example:
suppose last year daily sales equal to 100
here the results on 1/7/2019
here the results on 2/7/2019 and further:
any ideas how to solve this?
Hi.
First of all, do you have a proper date table in your model that is designated as such and covers all FULL years for your date columns in any of your tables? If you don't, then time-BI functions will not work correctly. To me it looks like you're using time-BI functions without a proper date table. Might be wrong, of course.
Best
Darek
Hi Darek
I do have a time dimension table generated by this Dax code:
Date = VAR BASECALENDAR = CALENDAR ( DATE ( 2009, 01, 01 ), TODAY () ) RETURN GENERATE ( BASECALENDAR, VAR BASEDATE = [DATE] VAR YEARDATE = YEAR ( BASEDATE ) VAR MONTHNUMBER = MONTH ( BASEDATE ) VAR MONTHNAME = FORMAT ( BASEDATE, "MMMM" ) VAR YEARMONTHNAME = FORMAT ( BASEDATE, "MMM-YY" ) VAR YEARMONTHNUMBER = ( YEARDATE * 12 + MONTHNUMBER ) - 1 RETURN ROW ( "DAY", BASEDATE, "YEAR", YEARDATE, "MONTH NUMBER", MONTHNUMBER, "MONTH", MONTHNAME, "YEAR MONTH NUMBER", YEARMONTHNUMBER, "YEAR MONTH", YEARMONTHNAME ) )
the sales fact table is linked to this dimension by inv date = dim date
Why would you have cross-filter enabled on the relationship? What's the use of it? You should switch to one-way filtering from dim to the fact table and then see what happens.
Hi
I've built a demo model to isolate the problem,
it's one to one relationship so I'm forced to set both ways join
however, it has nothing to do with my issue since in the original model it's one-way join and still the same wrong calculation.
I'm starting to believe that Sameperiodlastyear function is context related and was design to work like this
Hi there.
Here's the documentation of the function: https://dax.guide/sameperiodlastyear/ as I don't know how I could be of any more help...
Sorry.
Best
Darek
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 |
---|---|
48 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |