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

 

View solution in original post

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

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 274 members 3,078 guests
Please welcome our newest community members: