Home Forums Power Pivot Need help with VAR

Tagged: 

This topic contains 1 reply, has 1 voice, and was last updated by  KCantor 2 months, 2 weeks ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #11708

    KCantor
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    I am trying to write a variable for the current year. Here is what I would like but it does not work. Please help me with the last part. I must admit to being fairly behind in learning to write variables into calculations. Our data has not really needed them and is pretty straight forward. I need to write one, now, though, quicker than it would take for me to learn them. Can someone help me write the following variable calculation.

    Partial Prior Year =

    VAR

    FullYear = if the Max of ‘SalesPerformance'[OrderDate] is in the current year

    then “Yes”

    else “Yes” || “No”

    Return

    Calculate([Last Year Booked], [IsBeforeToday] = FullYear)

    Basically, if the max order date is in the current year, I want to filter the calculation to dates that are before today and if the max order date is in a prior year, I do not want to filter any of the dates.

     

    #11712

    KCantor
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    Here is what I used. I think I was making it too complicated.

    Partial Prior Year = IF(YEAR(MAX([OrderDate]) = YEAR(TODAY())),
    CALCULATE([Last Year Booked], FILTER(DimDate, [Is Before Today]=1)), CALCULATE([Last Year Booked], ALL(DimDate[Is Before Today])))
    </div>

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.