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.
Hi Can anyone tell me how to fix my DAX, when I filter year my last year is not populating,
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi , @zibster
If your dates are not continuous, it is recommended to create a date table.
Date = CALENDARAUTO()
And establish relationships between tables
Then you can try measure as below:
LY =
SUMX (
Sales,
CALCULATE ( SUM ( Sales[Sales] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)
The result will show as below.
Here is a demo
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Thanks for all the excamples but i still have the same problem that LY data is not populating when the year is selected, for excample i have 4 years of date 2017, 2018, 2019, 2020. When i sellect 2019 and 2018 I see LY sales only for 2019 and 2018 shows nothing, I was expecting to see LY sales for 2018 as well since i do have the data.
Hi , @zibster
Everything works well on my side. If I don’t know your data model, it is difficult to judge the specific reason.
Can you show me your table relationship view ?
Where does the “year” field using in the slicer come from, year in a date hierarchy or year created by yourself?
In addition ,please check again whether there is a problem with your data of "date" field.
Best Regards,
Community Support Team _ Eason
Hi,
The year is comming from date hierarchy, below is a sample of the data.
Thanks Z
Hi,
There is a problem in downloading the file from OneDrive. Upload it to Google Drive and share the download link.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish.
Thanks for your help on this 🙂 it works perfect.
Thanks
Z
You are welcome.
Hi @Ashish_Mathur I am trying to get the same results, but still not displaying Sameperiodlast Year Value. Have tried your soluction, but didn't work on my end. Please Help!
Hi,
Share some data and show the expected result.
Hi Thanks a lot for your Response. Initially I was working in a single table, but isoleted the dates into a calendar table as per your example and still didn't work. wen I filter for a specific Year it seems to return a total, but all the rows are empty. This is what I did.
1- created a Calendar Table :
Hi,
That information will not help me. share a small dummy dataset, explain the business context and clearly show the result that you are expecing so that i can frame a working solution.
Hi @Ashish_Mathur, the business scenario is that when a date range is selected from current period (eg: 1/1/2020 - 1/31/2020) all of the transations ( Every row in the date range) are selected and individual sales for both sales from (1/1/2020 - 1/31/2020) and ( 1/1/2019 - 1/31/2019) are displayed including the Diff in %.
Hi,
As i have requested, share a dummy dataset for me to work on.
Hi @Ashish_Mathur , I thnk this data set would translate my problem. I need to compare total sales in April 2019 to total sales in April 2018.
https://drive.google.com/file/d/1n9wlwvKn2I65uTbtcpO_9ybEOpwfkKW_/view?usp=sharing
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish,
Thanks for your feedback, but I found another solution.
Thanks,
Hi Ashish,
I have a Calendar table related to two other tables in a (1:*) relationship, but I am not able to correctly display data from both tables under the same visual. It only dsplays correctlybetween dates from the calendar table and one of the tables at a time. Otherwise I have a blank visual
This is the DAX I am using to create the calendar :
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 |
---|---|
116 | |
100 | |
69 | |
68 | |
44 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |