cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lord_kaiser Regular Visitor
Regular Visitor

DAX Forecasting Issue

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)

 

 

Capture.PNG

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

 

Any help is appreciated.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
lord_kaiser Regular Visitor
Regular Visitor

Re: DAX Forecasting Issue

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)

 

Capture 1.JPG

 

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)

 

Capture 2.JPG

 

Thank you

 

5 REPLIES 5
mattbrice Senior Member
Senior Member

Re: DAX Forecasting Issue

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 Regular Visitor
Regular Visitor

Re: DAX Forecasting Issue

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 Regular Visitor
Regular Visitor

Re: DAX Forecasting Issue

Hi Mate,

 

I managed to figure it out finally after some tinkering.

 

Thanks

Moderator v-yuezhe-msft
Moderator

Re: DAX Forecasting Issue

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lord_kaiser Regular Visitor
Regular Visitor

Re: DAX Forecasting Issue

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)

 

Capture 1.JPG

 

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)

 

Capture 2.JPG

 

Thank you