Forum Discussion

htbull_'s avatar
htbull_
Frequent Visitor
7 months ago
Solved

Current vs Prior Year Summation in a Measure

I am having trouble with a query that returns the value of a row for the current year correctly, but it is also placing the current year total in the previous year timeframe as well.

How can it be adjusted to return both the total year total and previous year total based on acctdate?

Query is down below:

CALCULATE (
SUM(PolicyPremium_History[USDPremiumChange]),
FILTER ( ALLSELECTED ( 'PolicyPremium_History' ), // Use ALLSELECTED to allow for slicers
'PolicyPremium_History'[Policy Effective] <= MAX ( 'Calendar_Disconnected'[Date] ) &&
'PolicyPremium_History'[Policy Expiration] > MAX ( 'Calendar_Disconnected'[Date] ) ),
FILTER(PolicyPremium_History, PolicyPremium_History[acctdate] <= MAX(Calendar_History[Date]) ))

The Calendar_Disconnected[Date] is being used to provide two separate dates (CY and PY, ex. 1/29/26 and 1/29/25), while the Calendar_History[Date] is being used in order to summarize premium changes whose account dates (acctdate) are before it. 

So while I may have a value of 1000 for a 10/1/2022 account date and 500 for 1/15/2023, I am returning 1500 for both dates rather than their respective values if I am selecting 1/30/23 for example.

 

I believe the problem may be the query line below:
FILTER(PolicyPremium_History, PolicyPremium_History[acctdate] <= MAX(Calendar_History[Date]) ))

Thanks in advance for the help!

Need Help
Show and Tell
Tips and Tricks
  • v-hashadapu's avatar
    v-hashadapu
    7 months ago

    Hi htbull_ , Thank you for reaching out to the Microsoft Community Forum.

     

    Please try this:
    Premium As Of =
    VAR AsOfDate =
    MAX ( 'Calendar_Disconnected'[Date] )
    RETURN
    CALCULATE (
    SUM ( PolicyPremium_History[USDPremiumChange] ),
    FILTER (
    ALLSELECTED ( PolicyPremium_History ),
    PolicyPremium_History[Policy Effective] <= AsOfDate &&
    PolicyPremium_History[Policy Expiration] > AsOfDate
    ),
    PolicyPremium_History[acctdate] <= AsOfDate
    )

    If it didn’t give expected output, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Include all the necessary details, detailing your scenario and issue as clearly and fully as possible. Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

     

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

4 Replies

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    7 months ago

    Hi htbull_ , Thank you for reaching out to the Microsoft Community Forum.

     

    Please try this:
    Premium As Of =
    VAR AsOfDate =
    MAX ( 'Calendar_Disconnected'[Date] )
    RETURN
    CALCULATE (
    SUM ( PolicyPremium_History[USDPremiumChange] ),
    FILTER (
    ALLSELECTED ( PolicyPremium_History ),
    PolicyPremium_History[Policy Effective] <= AsOfDate &&
    PolicyPremium_History[Policy Expiration] > AsOfDate
    ),
    PolicyPremium_History[acctdate] <= AsOfDate
    )

    If it didn’t give expected output, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Include all the necessary details, detailing your scenario and issue as clearly and fully as possible. Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

     

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

    • htbull_'s avatar
      htbull_
      Frequent Visitor
      7 months ago

      I made a couple of slight modifications on my end, but this worked nicely. Thank you!

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 months ago

    htbull_ 

    you can try this

     

    CY Total =
    CALCULATE (
    SUM(PolicyPremium_History[USDPremiumChange]),
    FILTER (
    ALLSELECTED(PolicyPremium_History),
    'PolicyPremium_History'[Policy Effective] <= MAX('Calendar_Disconnected'[Date]) &&
    'PolicyPremium_History'[Policy Expiration] > MAX('Calendar_Disconnected'[Date]) &&
    YEAR(PolicyPremium_History[acctdate]) = YEAR(MAX('Calendar_Disconnected'[Date])) &&
    PolicyPremium_History[acctdate] <= MAX('Calendar_Disconnected'[Date])
    )
    )

     

    PY Total =
    CALCULATE (
    SUM(PolicyPremium_History[USDPremiumChange]),
    FILTER (
    ALLSELECTED(PolicyPremium_History),
    'PolicyPremium_History'[Policy Effective] <= MAX('Calendar_Disconnected'[Date]) &&
    'PolicyPremium_History'[Policy Expiration] > MAX('Calendar_Disconnected'[Date]) &&
    YEAR(PolicyPremium_History[acctdate]) = YEAR(MAX('Calendar_Disconnected'[Date])) - 1 &&
    PolicyPremium_History[acctdate] <= DATEADD(MAX('Calendar_Disconnected'[Date]), -1, YEAR)
    )
    )

     

    if this does not work, pls provide some sample data and expected output