DAX Forecasting Issue

[lord_kaiser]

Hi There,

I have created a forecast by using the following DAX expression:

Actual Forecast = 
VAR
Revenue1YAGO= CALCULATE([Actual Revenue],DATEADD(Dates[Date],-1,YEAR))
VAR
Revenue2YAGO= CALCULATE([Actual Revenue],DATEADD(Dates[Date],-2,YEAR))
RETURN
DIVIDE(Revenue1YAGO + Revenue2YAGO ,2,0)

However when I try to seperate in a table with the following DAX:

Actual Type Forecast = CALCULATE([Actual Forecast],FILTER(VALUES('Types Only'[Types]),COUNTROWS(FILTER('Revenue Streams','Types Only'[Types]='Revenue Streams'[Revenue Groups]))))

It just shows me the same value for every type of revenue stream (See pic)

I've tried different things but just can't seem to get it to work.

Any help is appreciated.

Thanks

[mattbrice]

One potential isue I see is the use of COUNTROWS in a FILTER function.  In Dax, 0 = FALSE and any other number = TRUE.  So if COUNTROWS returns any number, the currrently iterated value of 'Revenue Streams' will be included.  Is this what you intended?  Why not remove the COUNTROWS function?

[lord_kaiser]

Hi MAte,

Thanks for the response.

If I were to remove the countrows, how would i then be able to split it based on the row names?

[lord_kaiser]

Hi Mate,

I managed to figure it out finally after some tinkering.

Thanks

[v-yuezhe-msft]

@lord_kaiser,

Glad to hear the issue is solved, you can share the correct DAX and accept your reply as answer to close this thread.

Regards,
Lydia

[lord_kaiser · Accepted solution]

Hi everyone,

Apologies for the delay. Here are the steps I used:

1.Create a supporting table. All my revenue groups were listed in the first column and in the second column i contained what type that revenue group belonged to (See Pic)

2. Then i created the following measure based on the above supporting table:

Revenue Type = CALCULATE([Actual Revenue],FILTER(VALUES('Master Commissions'[Revenue Type]),COUNTROWS(FILTER('Revenue Streams','Master Commissions'[Revenue Type]='Revenue Streams'[Revenue Groups]))))

3. I then created another measure based on the revenue type measure:

Actual Type Forecast = 
VAR
    Revenue1YAGO= CALCULATE([Revenue Type],DATEADD(Dates[Date],-1,YEAR))
VAR
    Revenue2YAGO= CALCULATE([Revenue Type],DATEADD(Dates[Date],-2,YEAR))
RETURN
DIVIDE(Revenue1YAGO + Revenue2YAGO ,2,0)

4. I was then able to split the revenue into a matrix as i wanted (See screen shot)

Thank you