Aggregation based on group by/ summarization and filter.

[rautaniket0077]

 Hi, i have attached the sample data now there are couple of things that i want to achieve using power bi-

1) first thing is i need to filter the table  based on the month-year selected from the slicer if aug-2024 is selected then i want to show me the data from aug-2023 to july 2024.

2) on the top of this filtered data, i need to calculate sum of these internal, external, insurance and warranty based on grouping by vin

3)after this based on summesion, i need to calculate the count of external when internal, warranty and insurance is 0

 you can use the archived date from table for doing the calculation

@mark_endicott ,  @johnt75 , @DataNinja777, @amitchandak, @lbendlin   and everyone else please help here. 

VIN	Date Archived	Mileage	Amount	External	Internal	Insurance	Warranty	Sum	Year	Brands
LACZZZ70ZXC003651	9/1/2023	21102	4536.6	3	0	0	0	3	1999	A
WV2ZZZ2KZEX118637	9/22/2023	136498	4973.6	4	0	0	0	4	2014	A
WV2ZZZ7HZGH110187	9/23/2023	29662	30884.73	12	2	0	0	14	2018	B
WV2ZZZ7HZGH110187	9/24/2023	29662	26706.64	7	1	0	0	8	2018	B
WV2ZZZ7HZ9H020352	9/25/2023	9201	92458.01	17	0	0	0	17	2008	B
WV2ZZZ7HZLH085028	9/26/2023	13304	16861.44	0	0	4	0	4	2021	B
WV1ZZZ2HZJH300149	9/27/2023	84426	99919.88	0	1	0	4	5	2019	C
WV2ZZZSK8NX066490	9/28/2023	166	3345.66	0	3	0	0	3	2023	C
WV2ZZZ7HZKH155931	9/29/2023	15755	273	0	3	0	0	3	2020	C
WV2ZZZ7HZNH050463	9/30/2023	27080	8535.68	0	1	0	4	5	2022	D
WV2ZZZ2KZEX090082	10/1/2023	118782	889.48	5	0	0	0	5	2014	D
WV2ZZZ7HZCH094885	10/2/2023	185262	1625	1	0	0	0	1	2012	D
Want calculation like below considering the date selected by user from slicer is aug-2024 , hence taking the data from aug-2023 to jul-2024
VIN	External	internal	insurance	warranty	sum
LACZZZ70ZXC003651	3	0	0	0	3
WV2ZZZ2KZEX118637	4	0	0	0	4
WV2ZZZ7HZGH110187	19	3	0	0	22
WV2ZZZ7HZ9H020352	17	0	0	0	17
WV2ZZZ7HZLH085028	0	0	4	0	4
WV1ZZZ2HZJH300149	0	1	0	4	5
WV2ZZZSK8NX066490	0	3	0	0	3
WV2ZZZ7HZKH155931	0	3	0	0	3
WV2ZZZ7HZNH050463	0	1	0	4	5
WV2ZZZ2KZEX090082	5	0	0	0	5
WV2ZZZ7HZCH094885	1	0	0	0	1
Expected output
External = 5

[rautaniket0077]

Hi@v-jiewu-msft, @tharunkumarRTK, @DataNinja777,@mark_endicott , @johnt75 , @DataNinja777, @amitchandak, @lbendlin,

The measure which @tharunkumarRTK given is working fine with the sample data but its giving me blank values in my current report instead i have used the below measures to calculate internal, external, warranty and insurance.

internal new =

var _date = MAX(Calender[Date])

var _result =

CALCULATE(

    SUM(Fact_Car_Service_Summary[internal]),

    DATESINPERIOD(

        Calender[Date],

        EOMONTH(_date,-1),

        -12,

        MONTH)

)

return _result

same way i have calculated for the rest.

now the thing is i just need the count whenever there is a sum of internal >0 and rest i.e. insurance, warranty and external is 0. I have uesed the below measure in which i have used IF but i know that this is not giving  me the count instead it is compairing and giving 1 and 0.

Internal cnt new corrected =

var _date = MAX(Calender[Date])

var _result =

CALCULATE(

    DISTINCTCOUNT(Fact_Car_Service_Summary[vin]),

    DATESINPERIOD(

        Calender[Date],

        EOMONTH(_date,-1),

        -12,

        MONTH

    )

)

RETURN

IF(

    [external new]+

    [warranty new]+

    [insurance new]=0,

    _result,

    0

)
Note :- i need to show the count in separate KPI card and not in table visual.

Thank you.

[v-jiewu-msft]

Hi,

Try creating a new page and re-adding the table data, metrics, and filters on that page to see if there are still issues. The expression provided above gives the correct results.

Best Regards,

Wisdom Wu

[PawarNovil]

Hello Aniket,

Based on the data and discussions in the posts, I have created a PBIX file that includes two solutions: one with a relationship and one without. Please take a look at the attached PBIX for further details.

attachment: https://drive.google.com/file/d/1gFeD3iqrO5eLYCfCuzsxfFH9ibRS2tkg/view?usp=sharing

------------------------------------------------------------------------------------------------

Regards,

Novil

If I answer your question, please mark my post as a solution.

[v-jiewu-msft]

Hi ,

If you have a problem with the data type, try changing the date type to date.

For the condition that the year to be equals to year of selected date, creating the new measure to filter the values.

Measure = 
var _year = SELECTEDVALUE('Date'[MonthYear])
RETURN
IF(YEAR(_year) = SELECTEDVALUE(Data[Year]), 1, 0)

Drag the measure to the table visual Filters pane and set show item is 1.

Best Regards,

Wisdom Wu

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

[tharunkumarRTK]

@rautaniket0077 

Please find the PBIX attached 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[rautaniket0077]

Hi @tharunkumarRTK, 
Thank you for the reply i guess this will work but the thing is my data is having some data type issue and hence my sum is giving me blank, is it possible to implement the same by using the archive date and not by using date from calender table.
Apart from this i have one more extra filter condition to add which is year which needed to be equals to year of selected date from the slicer (which is archived date).

 

[DataNinja777]

Hi @rautaniket0077 ,

Here’s the final version that works and dynamically counts the rows for the last 12 months:

External_Count_Only =
CALCULATE(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[External] > 0 &&
            'Table'[Internal] = 0 &&
            'Table'[Warranty] = 0 &&
            'Table'[Insurance] = 0
        )
    ),
    'Calendar'[Date] >= MAX('Calendar'[Date]) - 365
)

I have attached an example pbix file for your reference.

Best regards,

[rautaniket0077]

Hi @DataNinja777 ,
Thank you for the reply we want to exclude current month-year and -365 from your measure include it, for more details please check my post and my reply to @tharunkumarRTK .

Thank you.