Calculate with data from a measure

[Anonymous]

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:

Rolling 12 = CALCULATE(SUM('DSO Sales PDF'[Sales]),DATESINPERIOD('DSO Sales PDF'[Dateformat],ENDOFMONTH('DSO Sales PDF'[Dateformat]),-12,MONTH))

I've added this to my data table and it gives me the cumulated amount of last 12 months as I need:

Right now I need to use this data/ numbers to calculate with the date I have in my balance sheet:

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

[v-lili6-msft · Accepted solution]

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