Help with a complex problem please. Have tried 2 solutions.

[ben_har]

Hi.

I have a dataset that I am pulling from my work server including a bunch of information about business, projectid(distinct), created date of these projects and so on.

What I want to do is see the projects per month in line graph, which is quite simple with distinctcount(projects), put it in a graph with dates.
However what I also want to see is a running cummilative total of this ine a line chart. So if may(FY start) has 110 projects it will show 110 BUT, from June it should show June figure+May figure added to it, example; 110(may)+142(june)=252 june, then 252(prior)+150 of july= 402. This is just actual projects we made. Fiscal year start = 1st may, FY end = 30 april.
x-axis=date | y-axis = running cummulative total.

How can I make a target projects too with this? which I want as 167 May, 167(may)+167(june)=334, 334(may,june)+167(july)=501 and so on. (Note: there is not dataset for this and I would like a solution for it).
x-axis=date | y-axis = running total of "targets"

I was able to comeup with a formula that does this calculation, however no slicers work on this graph. Date slicer works, but other slicers like: Project status (draft, order, offer, rejected), region(south ,west,east, north), countries does not work at all.

Formula:

VAR CurrentMonth = SELECTEDVALUE(VT_Projects_Summary[Month_Year])

VAR FiscalYearStartMonth = 5  // May

VAR FiscalYearEndMonth = 4   // April

VAR FiscalYearStartDate =

    IF(

        MONTH(CurrentMonth) < FiscalYearStartMonth,

        DATE(YEAR(CurrentMonth) - 1, FiscalYearStartMonth, 1),

        DATE(YEAR(CurrentMonth), FiscalYearStartMonth, 1)

    )

RETURN

    IF(

        CurrentMonth < FiscalYearStartDate,

        0,

        SUMX(

            FILTER(

                ALL(VT_Projects_Summary),

                VT_Projects_Summary[Month_Year] >= FiscalYearStartDate && VT_Projects_Summary[Month_Year] <= CurrentMonth &&VT_Projects_Summary[ProjectType] = "Lowercost"

            ),

            [ProjectsPerMonth]

        )

    )

Other solutions tried:
1) giving index to each projectid but then again it slices projects by per month and not how I want them.
2) Making a loop in powerquery which does basically what I want but adding it to graph slices it again by per month.
3) giving a manual actual and target value to each month using calculated column but then for 31 days it just says "167", Also its not dynamic and highly manual process.

Any help is welcome!

[Anonymous · Accepted solution]

Hi @ben_har ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Measure = CALCULATE(DISTINCTCOUNT('Table'[projects]),FILTER(ALLSELECTED('Table'),'Table'[date]<=MAX('Table'[date])))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[ben_har]

Thank you that worked like a charm!

Sorry to others as I could not provide a dataset due to senstivity.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...