Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating a distinct table for each month using a Dates Table and another table

Hello there,

 

I have a table that has daily inputs for job orders that looks like this:

 

DateFull Plate NumberMonth
1/1/2021Job Order 1Jan
1/1/2021Job Order 2Jan
1/1/2021Job Order 3Jan
1/1/2021Job Order 4Jan
1/2/2021Job Order 3Jan
1/2/2021Job Order 4Jan
1/2/2021Job Order 5Jan
1/2/2021Job Order 6Jan
2/1/2021Job Order 5Feb
2/1/2021Job Order 6Feb
2/1/2021Job Order 7Feb
2/1/2021Job Order 8Feb
2/2/2021Job Order 7Feb
2/2/2021Job Order 8Feb
2/2/2021Job Order 9Feb
2/2/2021Job Order 10Feb
2/2/2021Job Order 11Feb

 

I'm trying to create a table using DAX that would give me a distinct count of a given month generating a table that looks like this:

 

MonthDistinct Item of that month
JanJob Order 1
JanJob Order 2
JanJob Order 3
JanJob Order 4
JanJob Order 5
JanJob Order 6
FebJob Order 5
FebJob Order 6
FebJob Order 7
FebJob Order 8
FebJob Order 9
FebJob Order 10
FebJob Order 11

 

so far I have tried..

JobOrderSummery =
Var _Date = GENERATE(CALENDAR(DATE(2021,1,1),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),VAR CurrentDate = [Date])
RETURN
CROSSJOIN(CALENDAR(DATE(2021,1,1),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),CALCULATETABLE(SUMMARIZE(MaintParetoTbl,MaintParetoTbl[Job Order Number]),MaintParetoTbl[Job Order Number] <> "",MaintParetoTbl[Date(Month)] = [Date] ))
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    amitchandak what ended up working thanks to your inspiration was

     

    CALCULATETABLE(CROSSJOIN(DISTINCT([Date(Month)]),DISTINCT([Job Order])),Filter)

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Have you tried like

    crossjoin(distinct(Table[Date Full]), Distinct( Table[Plate Number])

     

    or

     

    crossjoin(CALENDAR(DATE(2021,1,1),TODAY()), Distinct( Table[Plate Number])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak that is giving me all the months I only want the months that exist

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        amitchandak what ended up working thanks to your inspiration was

         

        CALCULATETABLE(CROSSJOIN(DISTINCT([Date(Month)]),DISTINCT([Job Order])),Filter)