Forum Discussion

samto22's avatar
samto22
Frequent Visitor
6 years ago

Forecasting Flat Revenue.

Hello! I've task to calculate projects forecasting revenue. We have project amount and need to break down this amount over project period. Let's say project amount is 250k€ and project period is 24...
Need Help
samto22's avatar
samto22
Frequent Visitor
6 years ago

Hi v-eachen-msft 

Here is a link to my sample file: Projects 

 

Seems I solved my above problem but now I'm facing another problem - how to link period(s) to Project YearMonth and show data in such manner:

 

2020-01           4 492 €
2020-02         25 343 €
2020-03           5 791 €
2020-04         13 093 €
2020-05         19 523 €
2020-06           8 084 €
2020-07         25 594 €
2020-08         15 571 €
2020-09           7 030 €
2020-10         31 079 €
2020-11         22 937 €
2020-12         12 784 €
2021-01           6 961 €
2021-02           4 812 €
2021-03           4 812 €
2021-04           3 600 €
2021-05           4 812 €
2021-06           4 812 €
2021-07           4 812 €
2021-08           4 812 €
2021-09           4 812 €
2021-10           4 812 €
2021-11           4 812 €
2021-12           4 812 €

 

Any suggestions on solving this issue would be very appreciated. 

 

Thanks!

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi samto22 ,

 

You could try this measure:

Measure =
FORMAT ( EDATE ( [mindate], SELECTEDVALUE ( Periods[Period] ) - 1 ), "yyyy-mm" )

 

  • samto22's avatar
    samto22
    Frequent Visitor
    6 years ago

    Hi v-eachen-msft 

     

    Thank you, but seems this measure does not work (as it returns nothing) or I do not understand it.

     

    Could you please explain a little bit more?

     

     

    • v-eachen-msft's avatar
      v-eachen-msft
      Community Support
      6 years ago

      Hi samto22 ,

       

      Sorry, [mindate] is a new measure. I create a more direct DAX:

      Measure =
IF (
    SELECTEDVALUE ( Periods[Period] ) <= MAX ( Projects[Duration, months] ),
    FORMAT (
        EDATE ( MIN ( Projects[ProjectStart] ), SELECTEDVALUE ( Periods[Period] ) - 1 ),
        "yyyy-mm"
    )
)

      Here is the result and test file for your reference.

       

      • samto22's avatar
        samto22
        Frequent Visitor
        6 years ago

        Hi v-eachen-msft 

         

        Thank you for your efforts, really appreciate!

         

        Now I'm faceing problem that if I remove Periods from the visual, data are showing in one line and, for example,  can not plot forecasting on chart with Year-Month on X-axis or add to another chart to compare with another calculations. What may be the way how to solve this?