cancel
Showing results for
Did you mean: Frequent Visitor

## DAX Projection Measure

```Hello everyone, everything good ?

I need help with DAX, I have the following projection calculation:

Projection Units Sold Column = CALCULATE('Measures Units'[Units Sold] + [Monthly Growth No.]/12;DATEADD('Dim Calendar'[Date];-12;MONTH))

This measure I'm taking the quantity sold in the previous year + Month-to-month growth variation of the previous year and dividing by 12 to get the current year's Projection

and I also have this DAX measure so that it doesn't show projection values ​​from previous months:

projection =
IF(ISNUMBER([Column Units Sold Projection]) && ISNUMBER('Units Measures'[Sold Units]); BLANK();[Column Units Sold Projection])

However, for some reason it is cutting my current month that should enter the Analysis as shown in the image below:I want to display the projection in the current month too In short, I want to create a measure that makes the projection show the values ​​for the current and future months only, I don't want the projection for past months to appear. It's possible?```
1 ACCEPTED SOLUTION  Community Support

Hi @ErickReiis ,

As your data is not available, please refer to the following template.

Base table: First ,create the below column about growth:

``````monthgrowcolumn =
VAR TEQ =
CALCULATE (
MAX ( 'table'[Date] ),
FILTER ( ALL ( 'table' ), 'table'[Date] < EARLIER ( 'table'[Date] ) )
)
RETURN
IF (
CALCULATE (
MAX ( 'table'[value] ),
FILTER ( ALL ( 'table' ), 'table'[Date] = TEQ )
)
= BLANK (),
0,
'table'[value]
- CALCULATE (
MAX ( 'table'[value] ),
FILTER ( ALL ( 'table' ), 'table'[Date] = TEQ )
)
)`````` Then we base on the table ,create a forecast table：

``Table2 = SELECTCOLUMNS('table',"Date",date(YEAR('table'[Date])+1,MONTH('table'[Date]),DAY('table'[Date])),"value",'table'[value]+AVERAGE('table'[monthgrowcolumn]))`` use the below measure:

``measure = IF(MAX(Table2[Date])<today(),0,1)``

Then create visual and filter:(As shown in the diagram, it shows data greater than the current month (which can be adjusted according to your actual needs) Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

5 REPLIES 5 Frequent Visitor
```Hello everyone, everything good ?

I need help with DAX, I have the following projection calculation:

Projection Units Sold Column = CALCULATE('Measures Units'[Units Sold] + [Monthly Growth No.]/12;DATEADD('Dim Calendar'[Date];-12;MONTH))

This measure I'm taking the quantity sold in the previous year + Month-to-month growth variation of the previous year and dividing by 12 to get the current year's Projection

and I also have this DAX measure so that it doesn't show projection values ​​from previous months:

projection =
IF(ISNUMBER([Column Units Sold Projection]) && ISNUMBER('Units Measures'[Sold Units]); BLANK();[Column Units Sold Projection])

However, for some reason it is cutting my current month that should enter the Analysis as shown in the image below: I want to display the projection in the current month too```

```
In short, I want to create a measure that makes the projection show the values ​​for the current and future months only, I don't want the projection for past months to appear. It's possible?```  Resolver II

Hey @ErickReiis ,

First of all,

In your first calculation (Projection Units sold column) you should add a filter which filter only if the date is pass the current date:
Projection Units Sold Column = CALCULATE('Measures Units'[Units Sold] + [Monthly Growth No.]/12;DATEADD('Dim Calendar'[Date];-12;MONTH)
Filter(ALL(Measures Units),
Measures Units[date] >= TODAY()
)

This will show none blanks value for previous date from today(You may adjust the today to get the current month if we are for example the 3rd june maybe "-1" on month should be appropriate).

Next, your current month is cut because in your second formula:
projection =
IF(ISNUMBER([Column Units Sold Projection]) && ISNUMBER('Units Measures'[Sold Units]); BLANK();[Column Units Sold Projection])

Your condition is that if you have projection and sold some units, then it shows blanks, but when it is your current month, well you have also projection and units sold so it shows blanks.

But since in the previous measure we defined a start date for the projection, we do not need a condition:
Projection = SUM(Projection Units Sold Column)

Best regards, Frequent Visitor
```Olá, obrigado por responder.
Fiz o que você comentou mas todas as projeções futuras desapareceram incluindo o mês atual.
Reforçando que essa parte chamada Medidas Unidades são medidas, então não posso usá-la dentro da função ALL pois ela também não possui nenhuma informação de data.
Tenho uma tabela de vendas que possui apenas as vendas registradas até o momento, e uma tabela calendário que possui datas futuras que são mostradas no gráfico de projeção.The measure:```
```Projection Units Sold = CALCULATE('Measures Units'[Units Sold] + [Monthly Growth Nº]/12;

DATEADD('Dim Calendar'[Date];-12;MONTH);FILTER(ALL('Dim Calendar');'Dim Calendar'[Date] >= TODAY()

))``` Frequent Visitor
```Hello everyone, everything good ?

I need help with DAX, I have the following projection calculation:

Projection Units Sold = CALCULATE('Measures Units'[Units Sold] + [Monthly Growth No.]/12;DATEADD('Dim Calendar'[Date];-12;MONTH))

This measure I'm taking the quantity sold in the previous year + Month-to-month growth variation of the previous year and dividing by 12 to get the current year's Projection

and I also have this DAX measure so that it doesn't show projection values ​​from previous months:

projection =
IF(ISNUMBER([Column Units Sold Projection]) && ISNUMBER('Units Measures'[Sold Units]); BLANK();[Column Units Sold Projection])

However, for some reason it is cutting my current month that should enter the Analysis as shown in the image below:``` I want to display the projection in the current month too

Projection and the others calculations are all measures, in short, I want the projections that from the following month onwards to appear dynamically.  Community Support

Hi @ErickReiis ,

As your data is not available, please refer to the following template.

Base table: First ,create the below column about growth:

``````monthgrowcolumn =
VAR TEQ =
CALCULATE (
MAX ( 'table'[Date] ),
FILTER ( ALL ( 'table' ), 'table'[Date] < EARLIER ( 'table'[Date] ) )
)
RETURN
IF (
CALCULATE (
MAX ( 'table'[value] ),
FILTER ( ALL ( 'table' ), 'table'[Date] = TEQ )
)
= BLANK (),
0,
'table'[value]
- CALCULATE (
MAX ( 'table'[value] ),
FILTER ( ALL ( 'table' ), 'table'[Date] = TEQ )
)
)`````` Then we base on the table ,create a forecast table：

``Table2 = SELECTCOLUMNS('table',"Date",date(YEAR('table'[Date])+1,MONTH('table'[Date]),DAY('table'[Date])),"value",'table'[value]+AVERAGE('table'[monthgrowcolumn]))`` use the below measure:

``measure = IF(MAX(Table2[Date])<today(),0,1)``

Then create visual and filter:(As shown in the diagram, it shows data greater than the current month (which can be adjusted according to your actual needs) Did I answer your question? Mark my post as a solution!

Best Regards

Lucien Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates. #### New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison! Top Solution Authors
Top Kudoed Authors
Users online (1,756)