Product sample control

Christianlekot · ‎02-21-2024

Hi,

I'am the manager of four producing industries and would like to check if my product analyses are frequent enough.

I have a online (not controlled by me) dataset with date, productions and product analyses, sites

The demanded frequenzy of testing shall not be less than one control every 200 tonnes or monthly whilst producing, whichever is more frequent (if product is produced).

I cant create tables because dataset i stored online but i can create measures.

I would like to create a measure that shows how many test a should have done, is this possible? And also a table that shows differece beween done tests and the demand.

I've tried but the only way i manage to solves it is to use calender month instead of monthly, but it nont good enough: code below:

Code for Demanded test

Krav prover =

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 1 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 2 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 3 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 4 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 5 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 6 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 7 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 8 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 9 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 10 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 11 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 12 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

Code for "done tests" - "demanded tests"

Status prov =

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 1 },

ALL('Datum TI slutmarkering'[Månad, siffra])

),0)

-

roundup(CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 1 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 2 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 2 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 3 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 3 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 4 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 4 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 5 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 5 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 6 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 6 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 7 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 7 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 8 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 8 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 9 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 9 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 10 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 10 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 11 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 11 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

+

roundup(

CALCULATE(

DISTINCTCOUNT('Kvalitetsdatarapporter'[Product order number]),

'Datum TI slutmarkering'[Månad, siffra] IN { 12 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

-

roundup(

CALCULATE(

SUM('Produktion'[Producerat antal]) / 200,

'Datum TI slutmarkering'[Månad, siffra] IN { 12 },

ALL('Datum TI slutmarkering'[Månad, siffra])

)

,0)

Kind regards Christian

Anonymous · ‎02-21-2024

Hi @Christianlekot ，

Here I will give you some suggestions on your code. According to your code, I think your code has a lot of duplicate parts that could be merged and simplified.

Krav prover =
ROUNDUP (
    CALCULATE (
        SUM ( 'Produktion'[Producerat antal] ) / 200,
        'Datum TI slutmarkering'[Månad, siffra]
            IN { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 },
        ALL ( 'Datum TI slutmarkering'[Månad, siffra] )
    ),
    0
)

Status prov =
/*Code for "done tests" - "demanded tests"*/
ROUNDUP (
    CALCULATE (
        DISTINCTCOUNT ( 'Kvalitetsdatarapporter'[Product order number] ),
        'Datum TI slutmarkering'[Månad, siffra]
            IN { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 },
        ALL ( 'Datum TI slutmarkering'[Månad, siffra] )
    ),
    0
)
    - ROUNDUP (
        CALCULATE (
            SUM ( 'Produktion'[Producerat antal] ) / 200,
            'Datum TI slutmarkering'[Månad, siffra]
                IN { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 },
            ALL ( 'Datum TI slutmarkering'[Månad, siffra] )
        ),
        0
    )

I think 'Datum TI slutmarkering'[Månad, siffra] should be the month column. As far as I know, there should be only 12 months in a year, so I am confused why you add the filter like 'Datum TI slutmarkering'[Månad, siffra] IN { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 } in your codes.

Is there something others like blank in [Månad, siffra]?

If this reply still couldn't help you solve your issue, please share a sample file with us. And you can show us a screenshot with the result you want. This will make us easier to find the solution.

Best Regards,
Rico Zhou

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

Christianlekot · ‎02-22-2024

Hi Thanks for the respons.

The reason i created one "cycle" for every month is that i want to roundup so if the produced amount is less than 200 tonnes i a month there shall be atleast one test.

There is no blanks in the [Månad, siffra], it is just [months, by number]

Is there a smart way to set up this "monthly" control instead of calender month?

This is a sample what im looking for, a measure that creates the test analysis column.

Notes	Date	Test, analysis	Produced amount
First test	2024-01-26	1	50
	2024-02-15		20
	2024-02-22		15
new test analysis because more than 1 month since last test analysis	2024-02-30	1	10
	2024-03-01		98
new test analysis because produced more than 200 tonnes since last test analysis.	2024-03-05	1	110

Sum		3	303

Product sample control

Helpful resources

FabCon Global Hackathon

FabCon Atlanta 2026

FabCon is coming to Atlanta

Product sample control

Helpful resources

FabCon Global Hackathon

FabCon Atlanta 2026