IF is not displaying value even though the condition is true

[iamhafsa]

I am new to Power BI and DAX and am stuck in an issue. I am using the following query:

 

NextNet =
var Q = VALUE(Sheet1[Mth_Year].[MonthNo])=1 && SWITCH(VALUE(Sheet1[Seq_no]),2,TRUE(),1,TRUE(),201706,TRUE(),201707,TRUE(),201708,TRUE(),201709,TRUE()) && Sheet1[Cal_Year]=2017 && Sheet1[Category_Code]="A000"

var N=  CALCULATETABLE(VALUES(Sheet1[Net]),VALUE(Sheet1[Mth_Year].[MonthNo])=2,SWITCH(VALUE(Sheet1[Seq_no]),2,TRUE(),1,TRUE(),201706,TRUE(),201707,TRUE(),201708,TRUE(),201709,TRUE()), Sheet1[Cal_Year]=2017, Sheet1[Category_Code]="A000")
RETURN IF(Q,N,0)

Q and N are working fine individually. But in the IF(Q,N,0) no value is displayed in the rows satisfying the condition Q.

I actually want the value of [Net] of February in NextNet of January. Any help would be highly appreciated.

 

 

[v-huizhn-msft]

Hi @iamhafsa,

From your formula, var Q will return Ture/False. While var N return a table. Your purpose is to get a table or value? In Power BI desktop, NextNet is a measure or you create a new table by "New Table" under Modeling on Home page? It's really confusing. Could you please share more details, post the sample table and list the expected result for further analysis?

Best Regards,
Angelia

[iamhafsa]

Hi @v-huizhn-msft

NextNet is a new column I am trying to create.

I want the Net value of next month with same seq_no as current month in Current Month. Eg: I want 5086.9 of February in NextNet column of January (Seq_no of both is 2 in this case). Likewise, I want to do it for all Seq_no.

[v-huizhn-msft]

Hi @iamhafsa,

You did it in excel. You want to get the expected result using DAX in Power BI desktop, or just in excel? If you want to do it in Power BI, please share your excel file rather than an screenshot. If you just want to do it in excel, please post it in office Excel forum.

Best Regards,
Angelia

[iamhafsa]

@v-huizhn-msft

I want to do this in Power BI with DAX. I just showed the expected output with a screen shot of excel.
I guess there is no way here to attach an excel file.

Thanks!

[v-huizhn-msft]

Hi @iamhafsa,

You can upload the excel file as attachments as follows.



Or you can store your .pbix file in onedrive, and post the share link.



Best Regards,
Angelia

[iamhafsa]

@v-huizhn-msft

Here's the OneDrive link: https://1drv.ms/u/s!Am1weOGDNKk3d_Gc_FDFIe-Hci8

[v-huizhn-msft · Accepted solution]

Hi @iamhafsa,

I download your sample table, and get the expected result.

1. Create a calculated column to get month.

Month = MONTH(Sheet1[Mth_Year])

2. Then create a NextNet to using the formula below.

NextNet = LOOKUPVALUE(Sheet1[Net],Sheet1[Cal_Year],Sheet1[Cal_Year],Sheet1[SeqNo],Sheet1[SeqNo],Sheet1[Month],Sheet1[Month]+1)

Please download the attachment and check the details.

Best Regards,
Angelia

[iamhafsa]

@v-huizhn-msft

Thank you so much for the help!