Forum Discussion

williamgood's avatar
williamgood
Frequent Visitor
2 years ago

Table with different date columns

HI,

I am producing a Power Bi Report where I need to use a table to display a list of values and then the count of last month and then year to date.

I have the date logged of each Item but cant seem to put the count value in two different columns with different date filters, I can only seem to do one or the other. Could anyone please help?

 

To look something like this

Item           Last Month Count       YTD Count

Coke           14                                200

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi williamgood ,

    There is a way to create a table with different date filters for each column is to use the DAX function CALCULATE to modify the filter context of each measure. For example, you can create two measures like this:

    Last Month Count =
CALCULATE ( COUNT ( Table[Item] ), PREVIOUSMONTH ( Table[Date] ) )
YTDCOUNT =
CALCULATE ( COUNT ( Table[Item] ), DATESYTD ( Table[Date] ) )

    These measures will count the number of items in the last month and year to date, respectively, based on the date column in your table. You can then add these measures to your table visual along with the item column.

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

    • williamgood's avatar
      williamgood
      Frequent Visitor
      2 years ago

      Hi Anonymous 

       

      Thank you for your help!

       

      I am getting an error when using this measure?

       

       

      Here is some sample data (please ignore the dates as these do not fall into last month, but I hope you get the idea)

      Order IDDateFull Closure Category Name
      SR0015090511/08/2023 07:45:00Coke
      SR0015090711/08/2023 08:05:00Pepsi
      SR0015091311/08/2023 08:22:00Pepsi
      SR0009767113/01/2023 10:22:09Orange
      SR0009767213/01/2023 10:27:23Cranbury
      SR0009767313/01/2023 10:27:37Coke