Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |