Group by and summarize

[Br1-981]

Hi, I got my raw data like this:

I need to derive, for each campaign the min "Date Start" and the max (Date End). I need also to keep the information about the Budget Owner associated to each campaign, this way:

I don't know if the bast way is to create 2 nex measurements or a new table to summarize everything in a group by style

Thanks in advance

Bruno

[Br1-981]

Hi,

I'm trying to use Custom Visual like Gantt (https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380765?tab=Overview).

Each campaign has many taks and each of them has a start/end date. I want to show campaigns with start date (so the minimum from its tasks) and end date (the maximum from its tasks), this way:

The problem is that into the variables it supporting the end date as maximum but not the start date:

If I add the start date I got the details of the tasks I don't want:

So my idea wa to create a new table where I don't have the task details so I create a table summary with:

Summary = SUMMARIZE(Weekly,Weekly[Budget Owner],Weekly[Campaign])

and on that table I added some calculated measurements:

MaxEndDate = 
VAR CampTable=
FILTER(
    ALL(Weekly),
    Weekly[Campaign] =SELECTEDVALUE(Weekly[Campaign])
)
RETURN
MAXX(CampTable,
Weekly[Date End].[Date]
)

MinStartDate = 
VAR CampTable=
FILTER(
    ALL(Weekly),
    Weekly[Campaign] =SELECTEDVALUE(Weekly[Campaign])
)
RETURN
MINX(CampTable,
Weekly[Date Start].[Date]
)

Spend = 
VAR CampTable=
FILTER(
    ALL(Weekly),
    Weekly[Campaign] =SELECTEDVALUE(Weekly[Campaign])
)
RETURN
SUM(
Weekly[Spend]
)

They are working properly as table:

but the visual does not accept calculated measurement as input so it is like I have to create the table, measurement inclued, in the first step with the SUMMARIZE

[sebastiandyrby · Accepted solution]

Hi again

It's true that the Gantt chart doesn't accept the measures as input to the start and/or end dates. However, a workaround is just to add the calculations of start and end dates as calculated columns instead and use these in the Gantt chart. 

The thing is that - depending on the size of your report - unneccesary tables may affect the performance. And here, I don't think it's necessary.

So just add calculated columns for you start and end dates like this:

And use these in the Gantt chart, like this: 

I then achieve this Gantt chart visual:

Hope this helps.

Best regards.

[rbouteldja]

Bonjour à tous,

Pourriez-vous m’aider ?! j’ai des rapports Excel sur Power BI online. J’ai mis des actualisations planifiées.

Or, un message d’erreur s’affiche à chaque actualisation.

ID d’activité: 8cf9aa78-e49d-4f17-b4b4-9d03e0328cc3

ID de demande: 55e656de-4c04-9708-c84e-f262341a0808

ID de corrélation: 6f57d558-4bf6-89e6-3f32-25f50cf50f95

Heure: Mon Feb 20 2023 10:18:23 GMT+0100 (heure normale d’Afrique de l’Ouest)

Version du service: 13.0.20105.47

Version du client: 2302.2.12402-train

URI du cluster: https://wabi-north-europe-h-primary-redirect.analysis.windows.net/

Est-ce que vous avez des explications ? ou des solutions ?

Je vous remercie par avance

Redouane,

[sebastiandyrby]

Hi Bruno

You can create a measure in which you filter the table to only look at the current campaign and then take the minimum of the chosen start dates. Like this:

So creating table visual and inputting the budget owner, the campaign number and then the measure above results in the following:

And for the end date, a similar measure is just created calculating the maximum instead.

Hope this helps.

Best regards

[Br1-981]

Thanks, the problem is that I need to pass those date to a visual that accept them just if they are from the same table.

So I created a New table using 

Summary = SUMMARIZE(Weekly,Weekly[Budget Owner],Weekly[Campaign],)

 To have the unique combination of Budget Owner and Campaigns but I do not know how to add the Columns including calculation of mix and max for Start and End Date

Thanks

[sebastiandyrby]

Hi again

Not perfectly sure what you're asking for and what you want to achieve. You should be able to use the columns you already have along with the new measures in any visual to achieve something like I showed in the table-screenshot. This could be done without creating a new table with SUMMARIZE.

But if you can explain more in detail what you want to achieve?

Best regards.