Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sandeep13
Helper II
Helper II

Show Previous month sales values in Card.

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 

 


 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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

5 REPLIES 5
v-binbinyu-msft
Community Support
Community Support

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.

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
    )
Mattiar
Helper I
Helper I

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.  

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

 

 

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()))

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.