Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello all,
I have table with sales and dates, the table is an aggregate table, it calculate the total sales for the month end.
it looks like this:
Product_Category Sales Amount Date
Bikes 29324230.99 20190131
Bikes 423410540.87 20190228
Jackets 480234.12 20190131
and so on.
I would like to create a measure for sales for previous month. so, I create date table and relate it to my sales table. I was able to get the correct sales amount and previous month sales amount, but the figure wouldn't be correct unless I use the date from calendar table, but that table has so many records and users have to scroll for a long time to select the correct date. when I use the date from the main table, the calcuation for the prior month wasn't correct. How can solve this issue?
Solved! Go to Solution.
Hi @mosman
Could you create a column that gets the max date for each month in your date table? Then create a helper table that is linked to your date table, with only the distinct values of that column?
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
Proud to be a Super User!
Hi @mosman
Could you create a column that gets the max date for each month in your date table? Then create a helper table that is linked to your date table, with only the distinct values of that column?
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
Proud to be a Super User!
Hi @mosman ,
Using the column from your date table, format it like this and make the title as shown.
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
Proud to be a Super User!
Hi Nathaniel_C,
the users would like to see one date at the time like this:
Hi @mosman ,
Is it possible to share the measure that does not work with us?
Thanks,
Nathaniel
Proud to be a Super User!
the measure looks like this:
when I use the date from the time intelligence table in the slicer, it looks like this:
but I want it to look like this:
Hi @mosman ,
Might you create a column in the Date table that gives you the max date for each month and use that for your slicer?
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
Proud to be a Super User!
User | Count |
---|---|
90 | |
88 | |
86 | |
79 | |
49 |
User | Count |
---|---|
153 | |
145 | |
106 | |
74 | |
55 |