User Profile
jppv20
Solution Sage
Joined 5 years ago
User Widgets
Contributions
Count records in previous time interval - Direct query mode
Hi, My table visual looks like this (Where "Count id" is a count of the records in that time interval and "Count id -5" should be a measure): What I need is the count of ids for the previous interval. Expected output for "Count id -5": Interval Count id Count id -5 4/23/2023 12:05:00 AM 343 251 4/23/2023 12:10:00 AM 291 343 4/23/2023 12:15:00 AM 241 291 4/23/2023 12:20:00 AM 241 241 4/23/2023 12:25:00 AM 254 241 4/23/2023 12:30:00 AM 239 254 Hope someone can help me out, thanks in advance! Best,Re: How to create table for first 3 month then Qtr 1, second 3 month Qtr 2 for
shri0025 I'm not sure if this is the easiest solution, but it worked for me: Go to the Query Editor and add a column for the Qtr: Duplicate the table twice, in one table you keep only the Month Name column and in the other the Qtr Column: Remove the duplicates in the Qtr column and change the name to Month Name Then append the last table to the second table and add an index column: Close & apply and go to relationships. Create two relationships between the tables: Table - Month Name to Table (2) - Month Name and Table - Qtr to Table (2) - Month Name (inactive). Then create this measure: Measure = var Sales = SUM('Table'[Total Sales]) return IF(ISBLANK(Sales),CALCULATE(SUM('Table'[Total Sales]),USERELATIONSHIP('Table'[Qtr],'Table (2)'[Month Name])),Sales) Put the measure and Month Name column from table 2 in the visual to get this result: Hope this helps! If I answered your question, please mark it as a solution to help other members find it more quickly.Re: How to create table for first 3 month then Qtr 1, second 3 month Qtr 2 for
shri0025 Are you sure this is what your data looks like when it is imported into PowerBi? In that case you can go to PowerQuery and add an index column: Then go back to the report and select the column Month Name --> Sort by column: Index Put Month Name and Total Sales in the Column chart and sort by Month: If I answered your question, please mark it as a solution to help other members find it more quickly.
