Periods comparaison

[DataVitalizer]

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 

[Anonymous · Accepted solution]

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.