Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Final client status by grouping on selected date range

Hi, I am fairly new Power BI user, currently working on interesting piece of DAX logic, which should not cause too much hasle, but I feel like I missing something in my code and cannot find out ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hello all,

    I have found solution to my problem. Here is the code:

     

    CountOfMaxStatus = COUNTROWS(
FILTER (
        TestData;
        TestData[TimeStamp]
            = CALCULATE (
                MAX ( TestData[TimeStamp] );
                FILTER(ALL(TestData);TestData[Client] = EARLIER ( TestData[Client]));
                FILTER(ALL(TestData);TestData[TimeStamp]>=MIN(DateTimeDimension[TimeStamp]));
                filter(ALL(TestData);TestData[TimeStamp]<=MAX(DateTimeDimension[TimeStamp]))
            )
))

    Here is link to the pbix file if you want to see it in the action: http://www.filedropper.com/maxstatusmeasure

     

    Thanks AlB and v-lili6-msft for the help.

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, Anonymous

You maybe take a look at these two Quick Measures as I think you want something like them.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

By the way, for CreatedDateTime is a datetime column with different time,

and Date table is only a datetime column with 12:00:00 AM.

So you‘d better use this formula to create a new CreatedDateTime

New CreatedDateTime = 'Raw LH'[CreatedDateTime].[Date]

then use this new date column for calculation.

 

Hope these can help you.

 

Best Regards,

Lin

 

 

 

 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Hi v-lili6-msft

 

I am quite curious about the

 

'Raw LH'[CreatedDateTime].[Date]

that you show as I had not seen it before. So you can access the components of date-type data with than syntax? Is this syntax used in other ways too or with other data types?  Can I read about it somewhere?

 

Is the above actually a shortcut for:

 

DATE(YEAR(Raw LH'[CreatedDateTime]), 
     MONTH(Raw LH'[CreatedDateTime]),
     DAY(Raw LH'[CreatedDateTime])
)

Thanks very much

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, AlB

    Yes, you could use this formula must before create a relationship with a date table. 

    , of course, you could use your formula,

    DATE(YEAR(Raw LH'[CreatedDateTime]), 
         MONTH(Raw LH'[CreatedDateTime]),
         DAY(Raw LH'[CreatedDateTime])
    )

     

    Best Regards,

    Lin

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Thanks v-lili6-msft

      Is thet syntax that you provide, with the dot, only available for dates  or in others cases as well? That's what I was most curious about.