Forum Discussion

isaki's avatar
isaki
Regular Visitor
3 years ago

How to create a measure

Hello,
Sorry this may be simple but I am new to Power BI.
I need to count the number of sales of the target items in the sales report like below. (colored cells)
The target Items is basically A, but B and C are added from depending on the year.
In the table below, I would like to get the number ”10” to be returned. How should I create a measure?

<Target Items>
04/01/2020 - 03/31/2021 -> "A"
04/01/2021 - 03/31/2022 -> "A","B"
04/01/2022 - 03/31/2023 -> "A","B","C"
04/01/2023 -> "A"

Any help would be greatly appreciated.

Need Help

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

     

     

     

    Target measure: = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            'Order',
            'Calendar'[Fiscal Year],
            'Calendar'[Year-Month],
            'Calendar'[Year-Month Sort],
            'Item'[Order Item],
            'Order'[Order ID]
        ),
        "@Target",
            VAR _fy = 'Calendar'[Fiscal Year]
            RETURN
                IF (
                    CALCULATE ( MAX ( 'Item'[Order Item] ) )
                        IN SUMMARIZE ( FILTER ( Target, Target[Fiscal Year] = _fy ), Target[Target] ),
                    1,
                    0
                )
    )
RETURN
    SUMX ( _t, [@Target] )

     

  • isaki's avatar
    isaki
    Regular Visitor
    3 years ago

    Hi Jihwan_Kim,
    Thank you for your reply!
    Is it possible to obtain the results without having to create a "target" table?
    In another similar report that I am currently working on, there are 7 types of items (A, B, C, D, E, F, G), and only "A" needs to be calculated after FY23. It would be even more helpful if I didn't have to add "A" to the target table every year.
    FY20 = "A"
    FY21 = "A", "B"
    FY22 = "A", "C"
    FY23,24,25..... = "A"