Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Sandeep13
Helper III
Helper III

Show Previous month sales values in Card.

‎03-06-2024 06:37 AM

Hi All,

I want to show Previous month sales values in Card for KPI use. I want values like below.

suppose my sales date table is present till 06 March 2024 and sales from 1st march to 03 march sales revenue is 120

Now I want to see only sales revenue for previous month from 1st Feb'24 to 03 Feb only.

Below is sample data for your refernce.

Expected ouptut from below table would be like below

Current Month Total_Count =114  ( Date 1st Mar To 3rd Mar) 

Previous month Total_Count =140         (Date from 1st feb to 3rd feb)

 

INSERT_DTYearQuarterMonthDayTotal_Count
1-Jan-242024Qtr 1January130
2-Jan-242024Qtr 1January245
3-Jan-242024Qtr 1January350
4-Jan-242024Qtr 1January453
5-Jan-242024Qtr 1January551
6-Jan-242024Qtr 1January649
7-Jan-242024Qtr 1January731
8-Jan-242024Qtr 1January852
9-Jan-242024Qtr 1January955
10-Jan-242024Qtr 1January1054
11-Jan-242024Qtr 1January1157
12-Jan-242024Qtr 1January1254
13-Jan-242024Qtr 1January1335
14-Jan-242024Qtr 1January1430
15-Jan-242024Qtr 1January1542
16-Jan-242024Qtr 1January1645
17-Jan-242024Qtr 1January1747
18-Jan-242024Qtr 1January1846
19-Jan-242024Qtr 1January1946
20-Jan-242024Qtr 1January2044
21-Jan-242024Qtr 1January2127
22-Jan-242024Qtr 1January2237
23-Jan-242024Qtr 1January2343
24-Jan-242024Qtr 1January2444
25-Jan-242024Qtr 1January2543
26-Jan-242024Qtr 1January2624
27-Jan-242024Qtr 1January2732
28-Jan-242024Qtr 1January2824
29-Jan-242024Qtr 1January2942
30-Jan-242024Qtr 1January3043
31-Jan-242024Qtr 1January3140
1-Feb-242024Qtr 1February153
2-Feb-242024Qtr 1February245
3-Feb-242024Qtr 1February342
4-Feb-242024Qtr 1February424
5-Feb-242024Qtr 1February549
6-Feb-242024Qtr 1February647
7-Feb-242024Qtr 1February746
8-Feb-242024Qtr 1February847
9-Feb-242024Qtr 1February944
10-Feb-242024Qtr 1February1031
11-Feb-242024Qtr 1February1123
12-Feb-242024Qtr 1February1245
13-Feb-242024Qtr 1February1351
14-Feb-242024Qtr 1February1445
15-Feb-242024Qtr 1February1547
16-Feb-242024Qtr 1February1649
17-Feb-242024Qtr 1February1745
18-Feb-242024Qtr 1February1825
19-Feb-242024Qtr 1February1944
20-Feb-242024Qtr 1February2047
21-Feb-242024Qtr 1February2145
22-Feb-242024Qtr 1February2249
23-Feb-242024Qtr 1February2345
24-Feb-242024Qtr 1February2440
25-Feb-242024Qtr 1February2527
26-Feb-242024Qtr 1February2644
27-Feb-242024Qtr 1February2745
28-Feb-242024Qtr 1February2846
29-Feb-242024Qtr 1February2945
1-Mar-242024Qtr 1March145
2-Mar-242024Qtr 1March242
3-Mar-242024Qtr 1March327

 @BI  @Expert @amitchandak 

 

‎


 

Labels:
Message 1 of 6
1,053 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎03-07-2024 12:07 AM

Hi @Sandeep13 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1709798667663.png

 

2. create measure with below dax formula

Current Month Total_Count =
VAR max_date =
    MAX ( 'Table'[INSERT_DT] )
VAR _month =
    MONTH ( max_date )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [INSERT_DT] <= max_date
            && MONTH ( [INSERT_DT] ) = _month
    )
RETURN
    SUMX ( tmp, [Total_Count] )

Previous month Total_Count =
VAR max_date =
    MAX ( 'Table'[INSERT_DT] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        MONTH ( [INSERT_DT] ) = MONTH ( max_date )
            && YEAR ( 'Table'[INSERT_DT] ) = YEAR ( max_date )
    )
VAR min_date =
    MINX ( tmp, [INSERT_DT] )
VAR _a =
    CALCULATE (
        MAX ( 'Table'[INSERT_DT] ),
        DATEDIFF ( 'Table'[INSERT_DT], min_date, DAY ) = 29
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[INSERT_DT] ),
        DATEDIFF ( 'Table'[INSERT_DT], max_date, DAY ) = 29
    )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [INSERT_DT] >= _a && [INSERT_DT] <= _b )
RETURN
    SUMX ( tmp1, [Total_Count] )

3. add two card visual with measure 

vbinbinyumsft_1-1709798679407.png

 

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.

View solution in original post

Show Previous month sales values in Card.pbix
Message 5 of 6
997 Views
5 REPLIES 5
Anonymous
Not applicable

‎03-07-2024 12:07 AM

Hi @Sandeep13 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1709798667663.png

 

2. create measure with below dax formula

Current Month Total_Count =
VAR max_date =
    MAX ( 'Table'[INSERT_DT] )
VAR _month =
    MONTH ( max_date )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [INSERT_DT] <= max_date
            && MONTH ( [INSERT_DT] ) = _month
    )
RETURN
    SUMX ( tmp, [Total_Count] )

Previous month Total_Count =
VAR max_date =
    MAX ( 'Table'[INSERT_DT] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        MONTH ( [INSERT_DT] ) = MONTH ( max_date )
            && YEAR ( 'Table'[INSERT_DT] ) = YEAR ( max_date )
    )
VAR min_date =
    MINX ( tmp, [INSERT_DT] )
VAR _a =
    CALCULATE (
        MAX ( 'Table'[INSERT_DT] ),
        DATEDIFF ( 'Table'[INSERT_DT], min_date, DAY ) = 29
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[INSERT_DT] ),
        DATEDIFF ( 'Table'[INSERT_DT], max_date, DAY ) = 29
    )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [INSERT_DT] >= _a && [INSERT_DT] <= _b )
RETURN
    SUMX ( tmp1, [Total_Count] )

3. add two card visual with measure 

vbinbinyumsft_1-1709798679407.png

 

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.

Show Previous month sales values in Card.pbix
Message 5 of 6
998 Views
Sandeep13
Helper III
Helper III
In response to Anonymous

‎03-07-2024 12:57 AM

Bro its worked for me. THANKS A TON😊

 

Just one query why you are using 29 in variable condition ?

 

VAR _a =
    CALCULATE (
        MAX ( asdada[INSERT_DT] ),
        DATEDIFF ( asdada[INSERT_DT], min_date, DAY ) = 29
    )
VAR _b =
    CALCULATE (
        MAX ( asdada[INSERT_DT] ),
        DATEDIFF ( asdada[INSERT_DT], max_date, DAY ) = 29
    )
Message 6 of 6
989 Views
0
Mattiar
Resolver I
Resolver I

‎03-06-2024 06:58 AM

Hi Sandeep,

 

you can try with:

SPLMonth = 
CALCULATE (

SUM ( total_count ), -- in general your basic measure       

FILTER (

ALL ( fact, column date),

column_date >= DATE ( YEAR(TODAY()), MONTH(TODAY())-1,1) && column_date <= TODAY()

)

)

 

Let me know if it is OK.  

Message 2 of 6
1,026 Views
0
Sandeep13
Helper III
Helper III
In response to Mattiar

‎03-06-2024 10:46 PM

Hi @Mattiar, 

Output is not showing as expected ,

Previous month should be Total_Count =140 of (1st Feb to 3rd feb) is what i want to show.     

 

Below SS for your reference.

Sandeep13_0-1709793819272.png

 

 

Message 3 of 6
1,001 Views
0
Mattiar
Resolver I
Resolver I
In response to Sandeep13

‎03-07-2024 12:16 AM

Right, one more condition is missing because if not, it goes up to today.
In the second part replace:

&& column_date <= TODAY() 

with 

&& DATE ( YEAR(TODAY()), MONTH(TODAY())-1, DAY ( TODAY()))

 

Message 4 of 6
994 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All