Forum Discussion

DataVitalizer's avatar
DataVitalizer
Super User
3 years ago
Solved

Periods comparaison

Hi Community,

I am having the below sample, I am trying to compare the sum of values from February with January.

Date          Values

1/1/2022   100

1/2/2022   100

1/3/2022   100

2/1/2022   100

2/2/2022   100

 

I created a measure to calculate sum from previousmonth, and into a table visual I showed Year and Month from a date table.

The result shows Feb 300 vs Jan 200, this results does not answer the need.

As long as the max date in january is 2/2/2022, how can I compare both months till the same day?

thank you in advance 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi DataVitalizer ,

    Please try below steps:

    1.below is my test table

    Table:

    2. create a measure with below dax formula

    Measure =
VAR max_date =
    MAXX ( ALL ( 'Table' ), [Date] )
VAR cur_year =
    YEAR ( max_date )
VAR cur_month =
    MONTH ( max_date )
VAR cur_day =
    DAY ( max_date )
VAR tmp1 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( [Date] ) = cur_year
            && MONTH ( [Date] ) = cur_month
    )
VAR tmp2 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( [Date] ) = cur_year
            && MONTH ( [Date] ) = cur_month - 1
            && DAY ( [Date] ) <= cur_day
    )
VAR cur_sum =
    SUMX ( tmp1, [Values] )
VAR pre_sum =
    SUMX ( tmp2, [Values] )
RETURN
    FORMAT ( max_date, "mmmm" ) & ":" & cur_sum & " 
  vs 
  "
        & FORMAT ( DATE ( cur_year, cur_month - 1, cur_day ), "mmmm" ) & ":" & pre_sum

    3. add a care visual with measure

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi DataVitalizer ,

    Please try below steps:

    1.below is my test table

    Table:

    2. create a measure with below dax formula

    Measure =
VAR max_date =
    MAXX ( ALL ( 'Table' ), [Date] )
VAR cur_year =
    YEAR ( max_date )
VAR cur_month =
    MONTH ( max_date )
VAR cur_day =
    DAY ( max_date )
VAR tmp1 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( [Date] ) = cur_year
            && MONTH ( [Date] ) = cur_month
    )
VAR tmp2 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( [Date] ) = cur_year
            && MONTH ( [Date] ) = cur_month - 1
            && DAY ( [Date] ) <= cur_day
    )
VAR cur_sum =
    SUMX ( tmp1, [Values] )
VAR pre_sum =
    SUMX ( tmp2, [Values] )
RETURN
    FORMAT ( max_date, "mmmm" ) & ":" & cur_sum & " 
  vs 
  "
        & FORMAT ( DATE ( cur_year, cur_month - 1, cur_day ), "mmmm" ) & ":" & pre_sum

    3. add a care visual with measure

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.