Forum Discussion

tools12345's avatar
tools12345
Icon for Helper I rankHelper I
4 years ago
Solved

How to set value in card based on week value

Hi everyone here in the community, we would like to ask for your insights and help, we have a specific card and a slicer with week. One requirement is that we should be able to display the current week amount on the card and once we select a week on the slicer, It will also display the week amount accordingly. Tried to create a measure but it shows the amount based on the maximum or last week on the table, and not on the current week. Below image and attach is the sample pbix file for testing. Thank you!! sample file 

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi tools12345 
    Here is th sample file with the solution https://we.tl/t-NAIGSrO6ds

     

    Selected Week Value = 
VAR SelectedWeek = COALESCE ( SELECTEDVALUE ( data1[Week] ), WEEKNUM ( TODAY ( ) ) - 1 )
VAR Result =
    CALCULATE ( 
        SUM ( data1[Account Payables] ),
        data1[Week] = SelectedWeek
    )
RETURN
    COALESCE ( Result, 0 )

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you very much for your message.

    Please try the below measure for replacing Blank with zero.

     

    expected outcome: = 
VAR _thisweeknumber =
    WEEKNUM ( TODAY (), 21 )
RETURN
    IF (
        ISFILTERED ( date_2022[Week of Year] ),
        SUM ( data1[Account Payables] ),
        CALCULATE (
            SUM ( data1[Account Payables] ),
            FILTER ( data1, data1[Week] = _thisweeknumber )
        )
    ) + 0

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I fully understood the data model, but please try to write the below measure and put it into the card visualization.

    Please check the below picture and the attached pbix file.

     

     

    expected outcome: =
VAR _thisweeknumber =
    WEEKNUM ( TODAY (), 21 )
RETURN
    IF (
        ISFILTERED ( date_2022[Week of Year] ),
        SUM ( data1[Account Payables] ),
        CALCULATE (
            SUM ( data1[Account Payables] ),
            FILTER ( data1, data1[Week] = _thisweeknumber )
        )
    )
    • tools12345's avatar
      tools12345
      Icon for Helper I rankHelper I
      4 years ago

      Thank you so much, Sir Jihwan_Kim -- truly thankful and you've been one of the active pro I can reach out here in the community 🙇🏻‍♀️ The measure works fine! How can I change the BLANK to 0 value for weeks that don't have an amount instead? Thank you! 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you very much for your message.

        Please try the below measure for replacing Blank with zero.

         

        expected outcome: = 
VAR _thisweeknumber =
    WEEKNUM ( TODAY (), 21 )
RETURN
    IF (
        ISFILTERED ( date_2022[Week of Year] ),
        SUM ( data1[Account Payables] ),
        CALCULATE (
            SUM ( data1[Account Payables] ),
            FILTER ( data1, data1[Week] = _thisweeknumber )
        )
    ) + 0
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi tools12345 
    Here is th sample file with the solution https://we.tl/t-NAIGSrO6ds

     

    Selected Week Value = 
VAR SelectedWeek = COALESCE ( SELECTEDVALUE ( data1[Week] ), WEEKNUM ( TODAY ( ) ) - 1 )
VAR Result =
    CALCULATE ( 
        SUM ( data1[Account Payables] ),
        data1[Week] = SelectedWeek
    )
RETURN
    COALESCE ( Result, 0 )

     

    • tools12345's avatar
      tools12345
      Icon for Helper I rankHelper I
      4 years ago

      Hi tamerj1 thank you so much! The measure works fine! How can I change the BLANK to 0 value for weeks that don't have an amount instead? 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi tools12345 
        The original reply has been updated as per your requirement.