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
Srijit
Regular Visitor

Need help on DAX

‎12-16-2021 11:39 PM

Need help on the below scenario

 

I have data for Year ,QTR,Mon,Week but the data(Total Count from Start) what we see is an accumiliated value. For Eg 2390 for Project 1 is not the count for that week alone, its from the start. My Requirement is as below 

 

1. Get the count value for Month (eg if OCT is selected in slicer them subtract the max of Nov with MAX of Oct)

2.Same way for QTR and Year

3.Finally when I select a Project and a month/QTR/Yr I should see value for the selected Project for the Month/QTR/Yr selected.

 

Tried to find out in google but couldnt get much help. PLease help on this issue

Project NameYearQTRMonth NameWeekTotal Count from start
Project 12021Q2Oct32390
Project 22021Q2Oct31390
Project 32021Q2Oct3890
Project 12021Q2Oct42400
Project 22021Q2Oct41400
Project 32021Q2Oct4900
Project 12021Q2Nov32410
Project 22021Q2Nov31410
Project 32021Q2Nov3910
Project 12021Q2Nov42430
Project 22021Q2Nov41440
Project 32021Q2Nov4950
Project 12021Q2Dec32470
Project 22021Q2Dec31450
Project 32021Q2Dec3970
Project 12021Q2Dec42475
Project 22021Q2Dec41455
Project 32021Q2Dec4975
Project 12022Q3Jan32480
Project 22022Q3Jan31475
Project 32022Q3Jan3988
Project 12022Q3Jan42495
Project 22022Q3Jan41490
Project 32022Q3Jan41000
Project 12022Q3Feb32500
Project 22022Q3Feb31500
Project 32022Q3Feb31050
Project 12022Q3Feb42550
Project 22022Q3Feb41560
Project 32022Q3Feb41100
Project 12022Q3Mar32600
Project 22022Q3Mar31600
Project 32022Q3Mar31500
Project 12022Q3Mar42800
Project 22022Q3Mar41700
Project 32022Q3Mar41600
Labels:
Message 1 of 6
824 Views
0
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

‎12-21-2021 10:38 PM

Hi @Srijit ,

According to your description, here's my solution.

1.Create a date column.

Date = FORMAT(DATE('Table'[Year],'Table'[Month Name],1),"yyyy-mm")

2.Create a rank column based on date.

Rank = RANKX(FILTER(ALL('Table'),'Table'[Project Name]=MAX('Table'[Project Name])),'Table'[Date],,ASC,Dense)

vkalyjmsft_0-1640154557962.png

3.Create the count per month measure.

Count per Month = 
VAR _Cur =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Project Name] = MAX ( 'Table'[Project Name] )
                && 'Table'[Rank] = MAX ( 'Table'[Rank] )
        ),
        'Table'[Total Count from start]
    )
VAR _Pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Project Name] = MAX ( 'Table'[Project Name] )
                && 'Table'[Rank]
                    = MAX ( 'Table'[Rank] ) - 1
        ),
        'Table'[Total Count from start]
    )
RETURN
    IF ( ISBLANK ( _Pre ), BLANK (), _Cur - _Pre )

4.Create the count per qtr measure.

Count per Qtr = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Project Name] = MAX ( 'Table'[Project Name] )
            && 'Table'[QTR] = MAX ( 'Table'[QTR] )
    ),
    [Count per Month]
) / 2

Get the expected result.

vkalyjmsft_2-1640154871962.png

As there isn't the start value here, so I show it blank.

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

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

12.22need help.pbix
Message 6 of 6
702 Views
0
5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

‎12-21-2021 10:38 PM

Hi @Srijit ,

According to your description, here's my solution.

1.Create a date column.

Date = FORMAT(DATE('Table'[Year],'Table'[Month Name],1),"yyyy-mm")

2.Create a rank column based on date.

Rank = RANKX(FILTER(ALL('Table'),'Table'[Project Name]=MAX('Table'[Project Name])),'Table'[Date],,ASC,Dense)

vkalyjmsft_0-1640154557962.png

3.Create the count per month measure.

Count per Month = 
VAR _Cur =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Project Name] = MAX ( 'Table'[Project Name] )
                && 'Table'[Rank] = MAX ( 'Table'[Rank] )
        ),
        'Table'[Total Count from start]
    )
VAR _Pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Project Name] = MAX ( 'Table'[Project Name] )
                && 'Table'[Rank]
                    = MAX ( 'Table'[Rank] ) - 1
        ),
        'Table'[Total Count from start]
    )
RETURN
    IF ( ISBLANK ( _Pre ), BLANK (), _Cur - _Pre )

4.Create the count per qtr measure.

Count per Qtr = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Project Name] = MAX ( 'Table'[Project Name] )
            && 'Table'[QTR] = MAX ( 'Table'[QTR] )
    ),
    [Count per Month]
) / 2

Get the expected result.

vkalyjmsft_2-1640154871962.png

As there isn't the start value here, so I show it blank.

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

12.22need help.pbix
Message 6 of 6
703 Views
0
Srijit
Regular Visitor

‎12-19-2021 09:08 AM

HI All, 

Is there any chance for a solution for the above issue ??

 

Message 5 of 6
719 Views
0
Srijit
Regular Visitor

‎12-17-2021 06:08 AM

@amitchandak  hope the infomation has the requirement you asked for 

Message 4 of 6
758 Views
0
Srijit
Regular Visitor

‎12-17-2021 02:29 AM

HI @amitchandak  Thank for responding

PLease find the expected column 

 

Data we HaveExpected to see from calculation
Project NameYearQTRMonth NameWeekTotal Count from startCount per MonthCount per QTR
Project 12021Q2Oct32390 Selected QTR - Previous QTR
Project 22021Q2Oct31390  
Project 32021Q2Oct3890  
Project 12021Q2Oct42400  
Project 22021Q2Oct41400  
Project 32021Q2Oct4900  
Project 12021Q2Nov32410  
Project 22021Q2Nov31410  
Project 32021Q2Nov3910  
Project 12021Q2Nov42430  
Project 22021Q2Nov41440  
Project 32021Q2Nov4950  
Project 12021Q2Dec32470  
Project 22021Q2Dec31450  
Project 32021Q2Dec3970  
Project 12021Q2Dec42475  
Project 22021Q2Dec41455  
Project 32021Q2Dec4975  
Project 12022Q3Jan32480  
Project 22022Q3Jan31475  
Project 32022Q3Jan3988  
Project 12022Q3Jan42495  
Project 22022Q3Jan41490  
Project 32022Q3Jan41000  
Project 12022Q3Feb32500  
Project 22022Q3Feb31500  
Project 32022Q3Feb31050  
Project 12022Q3Feb42550  
Project 22022Q3Feb41560  
Project 32022Q3Feb41100  
Project 12022Q3Mar32600  
Project 22022Q3Mar31600  
Project 32022Q3Mar31500  
Project 12022Q3Mar42800Diff of 2800(Max of March for Project 1)-2600 (Max of Feb for Project 1) 
Project 22022Q3Mar41700Diff of 1700(Max of March for Project 2)-1600 (Max of Feb for Project 2) 
Project 32022Q3Mar41600Diff of 1600(Max of March for Project 3)-1100 (Max of Feb for Project 3) 
Message 3 of 6
767 Views
0
amitchandak
Super User
Super User

‎12-17-2021 12:59 AM

@Srijit , Can you share what is expected outcome.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 6
776 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