Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Display data for dates relative to date selected in table

We use Power BI to publish our financial statements (e.g. balance sheet, profit & loss, etc).  Many of our statements also have a trailing 12-month version where it is the current month, and the prev...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Anonymous Thank you so much for the clarification, I understood that better than I did the video.  I've adjusted the calculations to be:

 

VAR MonthEndDate = SELECTEDVALUE('Date'[MonthEndDate])
VAR AdjMonth = DATEADD('Date'[MonthEndDate], -1, MONTH)
VAR SelPeriod = CALCULATETABLE(VALUES('Calendar'[MonthEndDate]),'Calendar'[MonthEndDate]=AdjMonth)
VAR Revenue = CALCULATE(SUM(Transactions[Net Change]), FILTER(Transactions, Transactions[Source]="Actual"), FILTER(Mapping, Mapping[PLcalc] = "Revenue")*-1, REMOVEFILTERS('Date'), KEEPFILTERS(SelPeriod), USERELATIONSHIP('Date'[Date], 'Calendar'[Date]))
VAR COSCloud = CALCULATE(SUM(Transactions[Net Change]), FILTER(Transactions, Transactions[Source]="Actual"), FILTER(Mapping, Mapping[PLcalc] = "COS - Cloud"), REMOVEFILTERS('Date'), KEEPFILTERS(SelPeriod), USERELATIONSHIP('Date'[Date], 'Calendar'[Date]))
VAR COSOther = CALCULATE(SUM(Transactions[Net Change]), FILTER(Transactions, Transactions[Source]="Actual"), FILTER(Mapping, Mapping[PLcalc] = "COS - Other"), REMOVEFILTERS('Date'), KEEPFILTERS(SelPeriod), USERELATIONSHIP('Date'[Date], 'Calendar'[Date]))
.....
 
RETURN
SWITCH (
TRUE (),

MAX('ReportConfig'[PLvar3]) = "blank", "",
MAX('ReportConfig'[PLvar3]) = "Revenue", FORMAT(Revenue, "$#,###;($#,###)"),
MAX('ReportConfig'[PLvar3]) = "COSCloud", FORMAT(COSCloud, "#,###;(#,###)"),
MAX('ReportConfig'[PLvar3]) = "COSOther", FORMAT(COSOther, "#,###;(#,###)"),
......
)

 

Now I'm getting the error:

 

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

Do you think this error is to do with the SelPeriod variable?  I used this approach (remove date filters, keep selperiod filter) in another BI report and it worked correctly.  The only difference between the two is the addition of the AdjMonth variable in this report.  Do I need to combine the AdjMonth and SelPeriod variables into one?

 

Thanks so much for your help!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Anonymous again this is very difficult to debug without sample data or properly formatted code, but the Revenue variable jumps out to me as not looking quite right.  You're multiplying a Filter argument by -1, when I think you're meaning to multiply the result of the CALCULATE statement by -1.

 

VAR Revenue = CALCULATE(SUM(Transactions[Net Change]), FILTER(Transactions, Transactions[Source]="Actual"), FILTER(Mapping, Mapping[PLcalc] = "Revenue")*-1, REMOVEFILTERS('Date'), KEEPFILTERS(SelPeriod), USERELATIONSHIP('Date'[Date], 'Calendar'[Date]))

 

 
Putting this in the proper format using https://www.daxformatter.com/ makes this more clear:

 

VAR Revenue =
CALCULATE (
    SUM ( Transactions[Net Change] ),
    FILTER ( Transactions, Transactions[Source] = "Actual" ),
    FILTER ( Mapping, Mapping[PLcalc] = "Revenue" ) * -1,
    REMOVEFILTERS ( 'Date' ),
    KEEPFILTERS ( SelPeriod ),
    USERELATIONSHIP ( 'Date'[Date], 'Calendar'[Date] )
)