Forum Discussion
Calculate with data from a measure
Hi All,
I think I have a complex case and I don't know if it's possible but I hope someone can help me.
I will try to explain my case as good as possible
I have 2 data sources that I would like to compare with each other (Sales and balance) with the next formula:
Sales from last 12 months/ the accumulated balance from that date*365.
So for example the calculation from may would be:
Total sales (June 2019 until may 2020)/acc. balance may 2020 * 365 = (result I need).
What I have right now is:
Sales data imported like this:
I have created a measure to get a rolling 12M data from the sales with the formula below:
If I put this data in a table I get the desired data I need per month (this data on the right column I would like to use in my formula):
It might is needed to add/change some relations, these are which I've right now:
I hope above information is clear and complete so you guys have a clear view of my case and what I would like to complete.
So it's like the result from the measure on the 12m rolling data is what I need to calculate with, but I can't really figure out how I could do that.
Any one can help me?
Thanks in advance!
Ramon
HI Anonymous
First, for your case, you are using Time-intelligence function,
I would suggest you add a dim date table as a bridge table to create the relationship between Sales and balance,
http://radacad.com/do-you-need-a-date-dimension
and you'd better use date filed to create the relationship instead of field that is YearMonth format.
If you still have the problem, please share your sample pbix file and your expected output.
Regards,
Lin
1 Reply
- v-lili6-msft
Community Support
HI Anonymous
First, for your case, you are using Time-intelligence function,
I would suggest you add a dim date table as a bridge table to create the relationship between Sales and balance,
http://radacad.com/do-you-need-a-date-dimension
and you'd better use date filed to create the relationship instead of field that is YearMonth format.
If you still have the problem, please share your sample pbix file and your expected output.
Regards,
Lin