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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TiaCamilian
Resolver I
Resolver I

Calculate code by weeks

 

I have a table with a startDateTime and Code1.

I need to be able to Filter the date using a calendar table by doing

FILTER(ALL('Calendar'[Date]), 
    AND('Calendar'[Date] >= TODAY(), 'Calendar'[Date] <= TODAY()+27)))

Then I have to CALCULATE the Code1 by categories 

Finally I will need to create a table that will have the weeks(WK), code1(Code) and Sum of the codes by category(CountOfCode)

 

I tried to use CALCULATE to SUM code1 by category and Filter by date but I am getting an error.

 

This is the code that is not working

sumcode = CALCULATE(SUM(MovesConfirm[Code1]),
FILTER(ALL('Calendar'[Date]), 
    AND('Calendar'[Date] >= TODAY(), 'Calendar'[Date] <= TODAY()+27)))

The error that I am getting is that SUM can only work with numbers.

Can someone please help? I an new to DAX.

 

 

The 1st table is the raw data and the 2nd table is what the final result.

Result.gif

 

 

1 ACCEPTED SOLUTION

HI @TiaCamilian ,

 

You can try to use following measure if it works for your requirement:

 

SumCode6 =
CALCULATE (
    COUNT ( MovesConfirm[Code1] ),
    ALLSELECTED ( 'MovesConfirm' ),
    VALUES ( 'MovesConfirm'[WeekNumber] )
)

 

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Correct, SUM will not work with text, perhaps try COUNT or COUNTA


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you for the suggestion. I tried the following:

SumCode6 = 
 CALCULATE(COUNT(MovesConfirm[Code1]),
FILTER(ALL('Calendar'), 
    AND('Calendar'[Date] >= TODAY(), 'Calendar'[Date] <= TODAY()+27)))

This is counting the Code1 colum by date. I need to calculate by week and needs to be between TODAY() and TODAY()+27

 

I created a colum with the following code

WeekNumber4 = IF(MovesConfirm[DateWithoutTime] = TODAY(), "WK1") & IF(MovesConfirm[DateWithoutTime] = Today()+1, "WK2")

To put WK1, WK2, WK3 and WK4 I need to do this repeted IF statement by TODAY() and TODAY()+ 1, etc. 

to distingush the weeks. I was hopping that once I have this code I can calculate the code1 by WeekNumber4?

 

This is an exaple of my new data:

 

Code1  DateWithoutTime  SumCode6 WeekNumber

Cof       2/19/2019               4                WK1

En        2/19/2019                1               WK1

Mai      2/19/2019                3               WK1

 

That SumCode6 is counting by date but I want to count by week.

 

Any help?

 

Thank you.

HI @TiaCamilian ,

 

You can try to use following measure if it works for your requirement:

 

SumCode6 =
CALCULATE (
    COUNT ( MovesConfirm[Code1] ),
    ALLSELECTED ( 'MovesConfirm' ),
    VALUES ( 'MovesConfirm'[WeekNumber] )
)

 

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

This works.

 

Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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