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.
Here is my data: https://ibb.co/1bH6QSF
I am trying to find the variance (difference) between the first and middle table in terms of expected sales and sale total (these are the actual sales), at the product category level (Table 3), for each year, quarter, and month.
How would I go about doing this? I suppose there are two preliminary steps:
1) Create a date-variable that links to the 3 tables.
2) Create a model that links the three tables together
The given date variables are weird, however, e.g. in table 1, I have "Year" and "month" in two separate columns. How do I consolidate this?
Ultimately, I want to create something similar to this: https://ibb.co/r4rgDfS
That can show me the "actual sales" (total sale in my data) vs "target sales" (forecasted sales in my data), at a Year, quarter, or month level.
Any help is appreciated, thank you!
Solved! Go to Solution.
Hi @Analytics4Life1 ,
new date = CONCATENATE("1/",MAX('Table (2)'[Month]))&"/"&max('Table (2)'[Year ])//You can use either CONCATENATE() (which only takes two values, or & to combine Text. Nee to use quotes around any text.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you.
Please read this post to get your question answered more quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Proud to be a Super User!
Hi Nathaniel, this is perfect, and I think I can take it from here 🙂
Hi @Analytics4Life1 ,
Actually I wrote that for a measure, not a column. So if you use it as a column, you don't need the MAX() functions. Sorry didn't see that until I looked again at the picture.
Nathaniel
Proud to be a Super User!
Hi @Analytics4Life1 ,
new date = CONCATENATE("1/",MAX('Table (2)'[Month]))&"/"&max('Table (2)'[Year ])//You can use either CONCATENATE() (which only takes two values, or & to combine Text. Nee to use quotes around any text.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you.
Please read this post to get your question answered more quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Proud to be a Super User!
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |