cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ErickReiis
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
Help.png 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
v-luwang-msft
Community Support
Community Support

Hi @ErickReiis ,

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

Base table:

vluwangmsft_0-1651633848164.png

 

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 )
            )
    )

vluwangmsft_1-1651633902279.png

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]))

vluwangmsft_2-1651633952250.png

 

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)

vluwangmsft_3-1651634006118.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

5 REPLIES 5
ErickReiis
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:

ErickReiis_0-1651498550321.png

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?

 

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,

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()
           
            ))

 

ErickReiis
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:

ErickReiis_0-1651581756984.png

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.

 

 
v-luwang-msft
Community Support
Community Support

Hi @ErickReiis ,

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

Base table:

vluwangmsft_0-1651633848164.png

 

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 )
            )
    )

vluwangmsft_1-1651633902279.png

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]))

vluwangmsft_2-1651633952250.png

 

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)

vluwangmsft_3-1651634006118.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors