Current year and Current year +1 now working

[mdaamirkhan]

Hi All

I need help on dax query as Current year and Current year +1 now working screenshot below and the query. If can provide the solution with dax  it will be good

Estimated revenue Current year +1 =

var thisYear = year(now())+1

var NSM = DISTINCTCOUNT(Input_Contract_Report[months])

var val =

CALCULATE( NSM,

Filter (Input_Contract_Report, Input_Contract_Report[Active] = "Yes"),

Filter ( Input_Contract_Report, Input_Contract_Report[End Date].[Year] = thisYear),

ALLEXCEPT(Invoice_Report,Invoice_Report[Contract Code])

)

return

--NSM

val * [Averge Revenue]

[selimovd · Accepted solution]

Hello @mdaamirkhan ,

you save NSM as a variable, but a variable is always static.

This means the moment you set the variable NSM the value is 10.

When you use this variable in the Calculate function, the value is already set to 10.

Try to use the DISTINCTCOUNT function in the CALCULATE function, then the result is evaluated in the right context:

Estimated revenue Current year +1 = 
VAR thisYear = YEAR ( NOW () ) + 1
VAR val =
    CALCULATE (
        DISTINCTCOUNT ( Input_Contract_Report[months] ),
        FILTER ( Input_Contract_Report, Input_Contract_Report[Active] = "Yes" ),
        FILTER (
            Input_Contract_Report,
            Input_Contract_Report[End Date].[Year] = thisYear
        ),
        ALLEXCEPT ( Invoice_Report, Invoice_Report[Contract Code] )
    )
RETURN
    --NSM
    val * [Averge Revenue]

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[mdaamirkhan]

its not working same issue. I have many-to-many relationship as well

[mdaamirkhan]

what about this var thisYear = YEAR(NOW())+1

will it work in the query. 

as per your query will it show 2022 value as well.

and if I want create table with a year use in slicer then how will used in the query