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,
I am having trouble with pulling Net Sales for the month of March. I am trying to compare current year sales to prior year sales and drill down. I am comparing Net Sales for the full year of 2019 to 2020. Everything is pulling correct besides the month of March. Today is 3/19/2020 and my prior year for the month of March continues to pull the entire month from 3/1/2019 to 3/31/2019 when I only want it to pull from 3/1/2019 to 3/18/2020. I would like my numbers to reflect the same time period for this March 2020 but it keeps pulling the entire month of March 2019. I am not sure how to manipulate my measure so it will only pull sales up to the 18th in the month of March in 2019. I have my measures listed below. Any help will be greatly appreciated.
My Data
2020 March Net Sales = 400
2019 March Net Sales = 1,200 // My March 2019 Net Sales should be lower than 1,200
My Net Sales Measure:
Solved! Go to Solution.
Hi @LyncoData1 ,
I‘ve tested your measure with my sample data and it works fine.You need to use the dimCalendar [date] as the slicer,then you will see:
Do remember to create a relationship between FactNetSales table and DimCalendar table.
Here is my .pbix file.
Hi @LyncoData1 ,
I‘ve tested your measure with my sample data and it works fine.You need to use the dimCalendar [date] as the slicer,then you will see:
Do remember to create a relationship between FactNetSales table and DimCalendar table.
Here is my .pbix file.
Thank you so much! This worked. However, the month of Feb in 2020 has duplicated the same values from Feb 28th to Feb 29th (159.20). I am not sure if it is because the month of Feb is a day longer compared to previous years where Feb is typically 28 days. When I only bring in Net Sales for 2019 the number does not duplicate. However, when I use PYTDSales measure the numbers duplicate themselves. Do you have any insight on how to fix?
When using time intelligence functions you generally need a separate date table.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
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 |
---|---|
105 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |