Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mysmsr
Frequent Visitor

Dax help

Hello experts,

Need help to write a new measure using DAX to calculate Rolling FC (Bias) where value gets calculated from a seleted month in the filter. Ex: bias month in this case is 3.

 

Bias
Filter ->  3  (coming from a filter table)


FC                Rolling FC   Rolling FC (Bias)
M1 10           10
M2 15           25
M3 20           45
M4 23           68                68
M5 15            83               83
M6 20            103             103
M7 23           126             126

Measure 1 Rolling FC
Measure 2 Rolling FC (Bias)

 

Any help/ direction would be highly appreciated. 

 

Thanks,

mymsr

 

3 ACCEPTED SOLUTIONS
mysmsr
Frequent Visitor

@johnyip  I managed to create the code as below. It's working fine for the year 22 but stops when  the year changes.

 

Rolling Forecast Bias =
                        IF(
                            MONTH(
                                MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,[Rolling Forecast (13 WK)],0
                        )
mysmsr_0-1676957425290.png

 

 

View solution in original post

Do you have a slicer to do the filtering? If so, what is the field you have used?

 

By reading the screencap of your calender table, maybe you can try the following:

 

Rolling Forecast Bias =IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,{{{ADD THE LOGIC THAT THE DATA YEAR IS LARGER THAN SELECTEDYEAR HERE}}}),[Rolling Forecast (13 WK)],0)

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

View solution in original post

@mysmsr , perhaps you can try to see if this works.

 

Rolling Forecast Bias =
VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table),
                             "Year",YEAR('FY Table'[Month])
                   )
VAR MinYear = VALUE(MINX(VirtualTable,[Year]))
RETURN
IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= MinYear),[Rolling Forecast (13 WK)],0)

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

View solution in original post

19 REPLIES 19
v-cgao-msft
Community Support
Community Support

Hi @mysmsr ,

 

Please try:

Rolling FC (Bias) =
CALCULATE (
    [Rolling FC],
    OFFSET (
        -3,
        ALLSELECTED ( 'FY Table'[Month] ),
        ORDERBY ( 'FY Table'[Month], ASC )
    )
)

vcgaomsft_0-1677029951888.png

 

OFFSET

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

v-cgao-msft, 

Thanks for your reply. I am not looking for offset value. I need to populate the same value in Rolling FC after few months based on the selected value from BIAS table. 
I understand the current code issue. Since we are using Month() funtion it is returing month number which is independent of FY year and our condition MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1 will not work.

Regards,

Raghu

mysmsr_0-1677031696456.png

 

Can I have a look of the structure and some sample data of Bias table?

 

Need to udnerstand why you wrote SELECTEDVALUE('Bias table'[Bias]), or more from your data structure to come up with the correct DAX.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Sure. Bias table is just a parameter table where I have  stored values of Bias and used it in the slicer to change the bias calculation dynamically in report. 

mysmsr_0-1677107274329.png

I have created a Fiscal year calendar table at month level using my fact table dates (IBP Accuracy Data).

mysmsr_1-1677107365432.png

In the fact table I have my FC and Actuals stored at Monthly level.

mysmsr_2-1677107690287.png

 

 

I think the most simple way is you can create another parameter table to store the value of [year], and then use the following DAX:

 

Rolling Forecast Bias =IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= SELECTEDVALUE('Year parameter table'[Year])),[Rolling Forecast (13 WK)],0)

 

 

Or, in response to your "filter on page to get last 14 months", the following might work (you need to test if there is an error since I haven't). No parameter table / slicer of year is needed for the following:

 

Rolling Forecast Bias =
VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table),
                             [Month],
                             "Year",MIN(YEAR('FY Table'[Month]))
                   )
VAR MinYear = MINX(VirtualTable,[Year])
RETURN
IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= MinYear),[Rolling Forecast (13 WK)],0)

 

 

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Thanks. Was trying Virtual table option. 

VAR Vtable = SUMMARIZE(ALLSELECTED('FY Table'),[Month],

                        "Year",MIN(YEAR('FY Table'[Month]))
)
VAR MinYear = MINX(Vtable,[Year])
return
                IF(OR(MONTH(MAX('FY Table'[Month]))>= SELECTEDVALUE('Bias table'[Bias])+1,
                            YEAR(MAX('FY Table'[Month]))>=MinYear),[Rolling Forecast (13 WK)],0)
getting error for the las column.
The MIN function only accepts a column reference as an argument.
Couldn't figureout any issue. Could you pl check..

 

@mysmsr , perhaps you can try to see if this works.

 

Rolling Forecast Bias =
VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table),
                             "Year",YEAR('FY Table'[Month])
                   )
VAR MinYear = VALUE(MINX(VirtualTable,[Year]))
RETURN
IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= MinYear),[Rolling Forecast (13 WK)],0)

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

@johnyip  Thanks that worked. I just had convert them to values to use it in my calculation. Really appreciate your help 🙏

 

Excellent!!!. That worked. Thanks a lot.

 

mysmsr
Frequent Visitor

@johnyip  I managed to create the code as below. It's working fine for the year 22 but stops when  the year changes.

 

Rolling Forecast Bias =
                        IF(
                            MONTH(
                                MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,[Rolling Forecast (13 WK)],0
                        )
mysmsr_0-1676957425290.png

 

 

Do you have a slicer to do the filtering? If so, what is the field you have used?

 

By reading the screencap of your calender table, maybe you can try the following:

 

Rolling Forecast Bias =IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,{{{ADD THE LOGIC THAT THE DATA YEAR IS LARGER THAN SELECTEDYEAR HERE}}}),[Rolling Forecast (13 WK)],0)

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Hello johnyip,

 

Just wandering if you can help me to fix below issue please..
 I have written below dax to get last month rolling actuals which is 15598152 for ytd. It works when I use card visual. But when I place it in matrix it changes. 

YTD Actuals =
        CALCULATE(
            [Rolling Actuals],
            YEAR('FY Table'[Month]) = YEAR(MAX('FY Table'[Month])) &&
        MONTH('FY Table'[Month]) = MONTH(MAX('FY Table'[Month]))
)

 And hence my graph gets wrong data when I plot YTD accuracy w.r.t month.

mysmsr_1-1677714909131.png

 

 

mysmsr_2-1677714974169.png

 

thanks.

msr

 

 

cannot quite understand. what is  your expected number of YTD actuals?



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Sorry. My ytd actual should be a constant value of last(max) month rolling actuals which is 15598152. This value should be the same across all months. Hence I tried to capture MAX of rolling actuals in my dax code.

 

Don't have the sample data and cannot test that. Please see if this works.

 

 

YTD Actuals =
VAR MaxMonth = MAXX(ALLSELECTED('FY Table'),[Month])
VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table),
                             [Month],
                             "Rolling Actual",[Rolling Actuals]
                   )
VAR Rolling_Actuals = CONCATENATEX(VirtualTable,[Rolling Actual],"|")
VAR Last_Rolling_Actual = PATHITEM(Rolling_Actuals,PATHLENGTH(Rolling_Actuals))
VAR Result = IF(MAX([Month]) <> BLANK(), Last_Rolling_Actual,BLANK())
RETURN
Result

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

I have one slicer for the BIAS value from 'Bias table'[Bias]. Other than that I have used filter on page to get last 14 months. Based on Fiscal Year table (FY table) month column.

 

mysmsr_0-1676957851176.png

 

 

How is your FY Table look like?



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!
mysmsr
Frequent Visitor

Hello johnyip,

 

Thanks for the prompt reply. my mistake i should below screen print. Month column is in dateformat from calendar table. not sure how I can share data from this platform.

mysmsr_1-1676945428332.png

 

 

mysmsr_0-1676945285331.png

Regards,

Raghu

 

johnyip
Super User
Super User

@mysmsr , from your sample, maybe your biased FC's measure is as simple as follows:

 

 

Rolling FC (Bias) = IF(MID(MAX([FC]),2,LEN(MAX([FC])))=ALLSELECTED([Your filter]),BLANK(),[Rolling FC])

 

Meanwhile, if you can provide your sample data, the answer will be more guaranteed to be correct.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.