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 Year3.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 issueProject NameYearQTRMonth NameWeekTotal Count from startProject 12021Q2Oct32390Project 22021Q2Oct31390Project 32021Q2Oct3890Project 12021Q2Oct42400Project 22021Q2Oct41400Project 32021Q2Oct4900Project 12021Q2Nov32410Project 22021Q2Nov31410Project 32021Q2Nov3910Project 12021Q2Nov42430Project 22021Q2Nov41440Project 32021Q2Nov4950Project 12021Q2Dec32470Project 22021Q2Dec31450Project 32021Q2Dec3970Project 12021Q2Dec42475Project 22021Q2Dec41455Project 32021Q2Dec4975Project 12022Q3Jan32480Project 22022Q3Jan31475Project 32022Q3Jan3988Project 12022Q3Jan42495Project 22022Q3Jan41490Project 32022Q3Jan41000Project 12022Q3Feb32500Project 22022Q3Feb31500Project 32022Q3Feb31050Project 12022Q3Feb42550Project 22022Q3Feb41560Project 32022Q3Feb41100Project 12022Q3Mar32600Project 22022Q3Mar31600Project 32022Q3Mar31500Project 12022Q3Mar42800Project 22022Q3Mar41700Project 32022Q3Mar41600

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

Forum Discussion

Srijit

Regular Visitor

4 years ago

Solved

Need help on DAX

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 Name	Year	QTR	Month Name	Week	Total Count from start
Project 1	2021	Q2	Oct	3	2390
Project 2	2021	Q2	Oct	3	1390
Project 3	2021	Q2	Oct	3	890
Project 1	2021	Q2	Oct	4	2400
Project 2	2021	Q2	Oct	4	1400
Project 3	2021	Q2	Oct	4	900
Project 1	2021	Q2	Nov	3	2410
Project 2	2021	Q2	Nov	3	1410
Project 3	2021	Q2	Nov	3	910
Project 1	2021	Q2	Nov	4	2430
Project 2	2021	Q2	Nov	4	1440
Project 3	2021	Q2	Nov	4	950
Project 1	2021	Q2	Dec	3	2470
Project 2	2021	Q2	Dec	3	1450
Project 3	2021	Q2	Dec	3	970
Project 1	2021	Q2	Dec	4	2475
Project 2	2021	Q2	Dec	4	1455
Project 3	2021	Q2	Dec	4	975
Project 1	2022	Q3	Jan	3	2480
Project 2	2022	Q3	Jan	3	1475
Project 3	2022	Q3	Jan	3	988
Project 1	2022	Q3	Jan	4	2495
Project 2	2022	Q3	Jan	4	1490
Project 3	2022	Q3	Jan	4	1000
Project 1	2022	Q3	Feb	3	2500
Project 2	2022	Q3	Feb	3	1500
Project 3	2022	Q3	Feb	3	1050
Project 1	2022	Q3	Feb	4	2550
Project 2	2022	Q3	Feb	4	1560
Project 3	2022	Q3	Feb	4	1100
Project 1	2022	Q3	Mar	3	2600
Project 2	2022	Q3	Mar	3	1600
Project 3	2022	Q3	Mar	3	1500
Project 1	2022	Q3	Mar	4	2800
Project 2	2022	Q3	Mar	4	1700
Project 3	2022	Q3	Mar	4	1600

Need Help

v-yanjiang-msft

4 years ago

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)

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.

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.pbix25 KB

5 Replies

amitchandak
Super User
4 years ago
Srijit , Can you share what is expected outcome.

Srijit

Regular Visitor

4 years ago

HI amitchandak Thank for responding

PLease find the expected column

Data we Have	Expected to see from calculation
Project Name	Year	QTR	Month Name	Week	Total Count from start	Count per Month	Count per QTR
Project 1	2021	Q2	Oct	3	2390		Selected QTR - Previous QTR
Project 2	2021	Q2	Oct	3	1390
Project 3	2021	Q2	Oct	3	890
Project 1	2021	Q2	Oct	4	2400
Project 2	2021	Q2	Oct	4	1400
Project 3	2021	Q2	Oct	4	900
Project 1	2021	Q2	Nov	3	2410
Project 2	2021	Q2	Nov	3	1410
Project 3	2021	Q2	Nov	3	910
Project 1	2021	Q2	Nov	4	2430
Project 2	2021	Q2	Nov	4	1440
Project 3	2021	Q2	Nov	4	950
Project 1	2021	Q2	Dec	3	2470
Project 2	2021	Q2	Dec	3	1450
Project 3	2021	Q2	Dec	3	970
Project 1	2021	Q2	Dec	4	2475
Project 2	2021	Q2	Dec	4	1455
Project 3	2021	Q2	Dec	4	975
Project 1	2022	Q3	Jan	3	2480
Project 2	2022	Q3	Jan	3	1475
Project 3	2022	Q3	Jan	3	988
Project 1	2022	Q3	Jan	4	2495
Project 2	2022	Q3	Jan	4	1490
Project 3	2022	Q3	Jan	4	1000
Project 1	2022	Q3	Feb	3	2500
Project 2	2022	Q3	Feb	3	1500
Project 3	2022	Q3	Feb	3	1050
Project 1	2022	Q3	Feb	4	2550
Project 2	2022	Q3	Feb	4	1560
Project 3	2022	Q3	Feb	4	1100
Project 1	2022	Q3	Mar	3	2600
Project 2	2022	Q3	Mar	3	1600
Project 3	2022	Q3	Mar	3	1500
Project 1	2022	Q3	Mar	4	2800	Diff of 2800(Max of March for Project 1)-2600 (Max of Feb for Project 1)
Project 2	2022	Q3	Mar	4	1700	Diff of 1700(Max of March for Project 2)-1600 (Max of Feb for Project 2)
Project 3	2022	Q3	Mar	4	1600	Diff of 1600(Max of March for Project 3)-1100 (Max of Feb for Project 3)

Srijit
Regular Visitor
4 years ago
amitchandak hope the infomation has the requirement you asked for
Srijit
Regular Visitor
4 years ago
HI All,
Is there any chance for a solution for the above issue ??

v-yanjiang-msft

Community Support

4 years ago

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)

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.

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.pbix25 KB

Forum Discussion

Need help on DAX

5 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX