Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

New Colum SUM based on date from another column

Hi all,

 

I've been searching through the forum and found some examples near to what I want to accomplish, but I think I'm having issues with syntaxis. 

 

I have a CALENDAR table, where I want to create a new column with the SUM value of completed orders from COMPLETIONS table based on Date=Date and filtering only by Shift (I will create 4 columns with the same formula but change the shift letter)

 

This are the assumptions for my case:

Column "07 - Final QC" in COMPLETIONS table is a date (e.g 01/01/2021) [You will find tons of completed orders here]

Column "Quantity completed" in COMPLETIONS table is a number (e.g. 25) [This number is the one I need to sum per day]

Column "Shift completed" in COMPLETIONS table is a letter (e.g. A)[Letter of the shift who completed the order)

 

Here's the formula I have so far. I want to accomplish the SUM of qty completed by day, filtered by shift:

 

QTY =
SUMX(
FILTER(
ALL('Archive2021',
Archive2021[07 - Final QC]),Archive2021[07 - Final QC].[Date]='Calendar'[DATE]
),
Archive2021[Quantity Completed]
)
 
I know that I'm mising the Shift filter, but that's where I got stuck.
If you could help me, I would really appreciate it.
 
Thanks!

 

Need Help

2 Replies

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Hi!

    As I'm unaware of your background in Power BI, I just have to ask - do you have to do this in dax?

    I'd just check that my date values between calendar and archive tables have a relationship, pull [Quantity Completed] as values to a graph, and Shift as legend or x-axis or how you want it. Though now that I realized you're using a date from a hierarchy, maybe there's more to it.

    Anyways, here's an untested suggestion if you somehow really need to do this in dax.

    Shift A QTY =
    Calculate(
        SUM( Archive2021[Quantity Completed] ),
        Archive2021[07 - Final QC].[Date]='Calendar'[DATE],
        Archive2021[Shift] = "A"
    )

    Or, just use your existing measure and pull [Shift] as legend / x-axis?
  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

    Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.
     
    Best Regards
    Lucien