Forum Discussion

jcamilo1985's avatar
jcamilo1985
Helper III
5 years ago
Solved

var dynamic time

Good Afternoon Experts As I always have to resort to your collaboration, I have a table (I want to make it graphical), in which this year, month, week of the month. The idea is to be able to see in...
  • v-alq-msft's avatar
    5 years ago

    Hi, jcamilo1985 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Previous Period Value = 
    IF(
        ISINSCOPE('Table'[Date].[Day]),
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
                ALL('Table'),
                'Table'[Date]=MAX('Table'[Date])-1
            )
        ),
        IF(
            ISINSCOPE('Table'[Date].[Month]),
            CALCULATE(
                SUM('Table'[Value]),
                FILTER(
                    ALL('Table'),
                    'Table'[Date].[MonthNo]=MAX('Table'[Date].[MonthNo])-1&&
                    'Table'[Date].[Year]=MAX('Table'[Date].[Year])
                )
            ),
            IF(
                ISINSCOPE('Table'[Date].[Quarter]),
                CALCULATE(
                    SUM('Table'[Value]),
                    FILTER(
                        ALL('Table'),
                        'Table'[Date].[QuarterNo]=MAX('Table'[Date].[QuarterNo])-1&&
                        'Table'[Date].[Year]=MAX('Table'[Date].[Year])
                    )
                ),
                CALCULATE(
                    SUM('Table'[Value]),
                    FILTER(
                        ALL('Table'),
                        'Table'[Date].[Year]=MAX('Table'[Date].[Year])-1
                    )
                )
            )
        )
    )

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.